SQL ORDER BY and LIMIT — Sorting and Pagination

Sorting and Paginating Results

ORDER BY controls the order of returned rows. LIMIT (or FETCH FIRST) restricts the number of rows. Combined, they power pagination.

Basic Sorting

SELECT name, salary
FROM employees
ORDER BY salary DESC;         -- highest first

SELECT name, hire_date
FROM employees
ORDER BY hire_date ASC;       -- oldest first (ASC is default)

Multi-Column Sort

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

LIMIT and OFFSET

-- First 10 rows
SELECT * FROM employees ORDER BY id LIMIT 10;

-- Page 3, 10 rows per page (skip 20, return 10)
SELECT * FROM employees ORDER BY id LIMIT 10 OFFSET 20;

Dialect Differences

Database	Syntax
PostgreSQL	LIMIT n OFFSET m or FETCH FIRST n ROWS ONLY
MySQL	LIMIT [offset,] count or LIMIT count OFFSET offset
SQLite	LIMIT n OFFSET m
SQL Server	OFFSET m ROWS FETCH NEXT n ROWS ONLY

Pagination Best Practices

Offset pagination (LIMIT/OFFSET) is simple but slow on deep pages because the database must scan and discard skipped rows.

Keyset pagination (seek method) is faster for large datasets:

-- Instead of OFFSET, use WHERE with the last seen value
SELECT * FROM employees
WHERE id > 1000          -- last id from previous page
ORDER BY id
LIMIT 10;

NULL Ordering

-- PostgreSQL: control where NULLs appear
SELECT * FROM employees
ORDER BY manager_id NULLS LAST;

-- MySQL: NULLs are treated as the lowest value
SELECT * FROM employees
ORDER BY manager_id ASC;   -- NULLs first