Sorting and Limiting

Concept

ORDER BY sorts the result set. Without it, row order is not guaranteed — not insertion order, not primary key order.

Row-limiting clauses (LIMIT, OFFSET, TOP, FETCH FIRST) return a subset of rows, used for Top-N queries and pagination.

Why It Matters

Reports, leaderboards, and UIs need deterministic ordering.

Pagination is fundamental to web and mobile applications.

Top-N queries are a core interview topic.

Syntax

Skip M then take N LIMIT N or FETCH FIRST N ROWS ONLY LIMIT N OFFSET M or OFFSET M ROWS FETCH NEXT N ROWS ONLY MySQL / MariaDB LIMIT N OFFSET M or LIMIT M, N LIMIT N OFFSET M SELECT TOP (N)...

ORDER BY... OFFSET M ROWS FETCH NEXT N ROWS ONLY (2012+; requires ORDER BY) FETCH FIRST N ROWS ONLY OFFSET M ROWS FETCH NEXT N ROWS ONLY Oracle 11g and older

WHERE ROWNUM <= N (on an ordered subquery) Nested ROWNUM subqueries

SELECT columns
FROM table
ORDER BY col1 [ASC | DESC] [NULLS FIRST | NULLS LAST], col2 [ASC | DESC], ...

Example

9.1 ORDER BY ASC / DESC Result of DESC:

9.2 Sorting by multiple columns The second key only matters when the first key ties; the third breaks remaining ties (Meera before Neha alphabetically).

9.3 Sorting by alias, expression, or position 9.4 NULL ordering NULLs in ASC order NULLs in DESC order NULLS FIRST/LAST supported No (use ORDER BY col IS NULL, col)

No (use CASE WHEN col IS NULL THEN 1 ELSE 0 END) 9.5 Top-N queries Top 3 most expensive products:

Ties: to include rows tied with the last one:

With ties: Asha 250000, Vikram 150000, Rahul 120000 (no tie at 3rd place, so 3 rows). Try FETCH FIRST 4 ROWS WITH TIES: Asha, Vikram, Rahul, Neha, Meera (5 rows, because the 4th value 95000 is shared).

Oracle 11g style:

(Applying ROWNUM directly with ORDER BY in the same query filters before sorting, which is wrong.) 9.6 Pagination (OFFSET)

Page size 2, page 2 (rows 3–4) of products ordered by product_id:

Formula: OFFSET = (page_number − 1) × page_size.

Keyset (seek) pagination — faster for deep pages because OFFSET must read and discard all skipped rows:

SELECT product_name, price FROM products ORDER BY price;        -- ASC is default
SELECT product_name, price FROM products ORDER BY price DESC;

SELECT first_name, dept_id, salary
FROM employees
WHERE dept_id IS NOT NULL
ORDER BY dept_id ASC, salary DESC, first_name ASC;

SELECT product_name, price * stock AS stock_value
FROM products
ORDER BY stock_value DESC;           -- alias allowed in ORDER BY

SELECT product_name, price FROM products ORDER BY LENGTH(product_name);   -- expression (LEN in SQL Server)

SELECT product_name, price FROM products ORDER BY 2 DESC;  -- position: 2nd column (legal but fragile)

-- PostgreSQL / Oracle / SQLite
SELECT first_name, dept_id FROM employees ORDER BY dept_id NULLS FIRST;

-- MySQL: put NULLs last in ascending order
SELECT first_name, dept_id FROM employees ORDER BY dept_id IS NULL, dept_id;

-- SQL Server: put NULLs last
SELECT first_name, dept_id FROM employees
ORDER BY CASE WHEN dept_id IS NULL THEN 1 ELSE 0 END, dept_id;

-- PostgreSQL, MySQL, SQLite
SELECT product_name, price FROM products ORDER BY price DESC LIMIT 3;

-- SQL Server
SELECT TOP (3) product_name, price FROM products ORDER BY price DESC;

-- Standard SQL: PostgreSQL, Oracle 12c+, SQL Server 2012+ (needs OFFSET), Db2
SELECT product_name, price FROM products ORDER BY price DESC FETCH FIRST 3 ROWS ONLY;
-- SQL Server form:
SELECT product_name, price FROM products ORDER BY price DESC OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY;

-- SQL Server
SELECT TOP (3) WITH TIES first_name, salary FROM employees ORDER BY salary DESC;
-- PostgreSQL 13+, Oracle 12c+
SELECT first_name, salary FROM employees ORDER BY salary DESC FETCH FIRST 3 ROWS WITH TIES;

SELECT * FROM (
    SELECT product_name, price FROM products ORDER BY price DESC
) WHERE ROWNUM <= 3;

-- PostgreSQL, MySQL, SQLite
SELECT product_id, product_name FROM products ORDER BY product_id LIMIT 2 OFFSET 2;

-- SQL Server, Oracle 12c+, PostgreSQL
SELECT product_id, product_name FROM products
ORDER BY product_id
OFFSET 2 ROWS FETCH NEXT 2 ROWS ONLY;

-- "Next page after product_id 104"
SELECT product_id, product_name
FROM products
WHERE product_id > 104
ORDER BY product_id
LIMIT 2;           -- 105, 106

Expected Result

The two most recent orders:

SELECT order_id, order_date, total_amount
FROM orders
ORDER BY order_date DESC
LIMIT 2;               -- SQL Server: SELECT TOP (2) ...

Common Mistakes

Using LIMIT/TOP without ORDER BY: "top 3" becomes "any 3".

Non-unique ORDER BY with pagination: rows with equal sort keys can appear on two pages or be skipped. Always add a unique tie-breaker (ORDER BY price DESC, product_id).

Using ROWNUM <= N with ORDER BY in the same Oracle query block.

Using LIMIT in SQL Server or TOP in PostgreSQL.

Deep OFFSET pagination on huge tables (OFFSET 1000000) — slow.

Relying on ORDER BY inside a subquery or view to order the final result (not guaranteed).

Best Practices

Always include a deterministic tie-breaker in ORDER BY.

Prefer FETCH FIRST for portable code where supported.

Use keyset pagination for large datasets and infinite scrolling.

Index the columns used in ORDER BY for frequent Top-N queries.

Interview Relevance

High: "How do you get the top 5 salaries?", "LIMIT vs TOP vs FETCH?", "How does pagination work?", "What is the problem with OFFSET for large pages?", "How are NULLs sorted?".

Practice Questions

List employees by hire date, oldest first.

Show the 2 cheapest products.

Show page 3 of customers with page size 2, ordered by name.

Sort customers so that those without an email appear last, in your DBMS.

Rewrite SELECT TOP 5 * FROM orders ORDER BY total_amount DESC for PostgreSQL and Oracle.