ORDER BY & Pagination

The ORDER BY keyword is used to sort the result-set in ascending or descending order.

Syntax

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

Sorting Order

• ASC: Ascending (default)
• DESC: Descending

Multi-Column Sorting

You can sort by multiple columns. SQL sorts by the first column; for rows with identical values in the first column, it uses the second column, and so on.

SELECT * FROM employees ORDER BY department_id ASC, salary DESC;

NULL Handling in Sorting

NULL behavior varies by database:

• PostgreSQL: NULLs are considered larger than any value — they appear last in ASC, first in DESC.
• MySQL: NULLs appear first in ASC, last in DESC.
• SQL Server: NULLs are smallest — they appear first in ASC, last in DESC.

Override with NULLS FIRST or NULLS LAST (PostgreSQL, Oracle):

SELECT * FROM employees ORDER BY salary DESC NULLS LAST;

Pagination with LIMIT and OFFSET

When result sets are large, you paginate by specifying how many rows to return and where to start:

-- Page 1: rows 1-10
SELECT * FROM employees ORDER BY employee_id LIMIT 10 OFFSET 0;

-- Page 2: rows 11-20
SELECT * FROM employees ORDER BY employee_id LIMIT 10 OFFSET 10;

Performance note: Large OFFSET values are slow because the database still scans all skipped rows. For deep pagination, use keyset pagination:

-- Keyset pagination (efficient for deep pages)
SELECT * FROM employees WHERE employee_id > 1000 ORDER BY employee_id LIMIT 10;

ORDER BY with Expressions

You can sort by computed values:

SELECT name, salary * 12 AS annual FROM employees ORDER BY annual DESC;

ORDER BY and Indexes

Sorting an unindexed column on a large table triggers a “filesort” operation, which is expensive. Adding an index on the sort column can dramatically speed up ORDER BY queries.

SELECT * FROM employees
ORDER BY salary DESC;

SELECT * FROM employees
ORDER BY department_id ASC, salary DESC;

SELECT salary FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 2;

-- For PostgreSQL/MySQL
SELECT * FROM employees
ORDER BY RANDOM() -- or RAND() in MySQL
LIMIT 5;

SELECT * FROM employees
ORDER BY employee_id
LIMIT 10 OFFSET 10;

SELECT * FROM employees
WHERE employee_id > 100
ORDER BY employee_id
LIMIT 10;