ORDER BY, LIMIT and Pagination

Concept

A relational table has no order. A result set has one only if you ask for it. ORDER BY sorts the result; LIMIT (or its dialect equivalent) keeps only the first rows of that sorted result.

Syntax

SELECT column_list
FROM   table_name
ORDER  BY column1 [ASC|DESC], column2 [ASC|DESC]
LIMIT  row_count OFFSET skip_count;

Example

-- Single column, descending
SELECT first_name, salary
FROM   employees
ORDER BY salary DESC;

-- Multiple columns: department ascending, then salary descending inside it
SELECT dept_id, first_name, salary
FROM   employees
ORDER BY dept_id ASC, salary DESC;

-- Sorting by an expression, and by a select list alias
SELECT first_name, salary * 12 AS annual
FROM   employees
ORDER BY annual DESC;

-- Top 3 earners
SELECT first_name, salary
FROM   employees
ORDER BY salary DESC
LIMIT 3;

Explanation

Multi column sorting is applied left to right: rows are ordered by dept_id first, and salary DESC only breaks ties within a department. Each column gets its own direction; ASC is the default.

ORDER BY can use a select list alias because SELECT is evaluated before ORDER BY. That is the one place an alias is allowed.

Pagination across dialects

-- The standard form: PostgreSQL, SQL Server 2012+, Oracle 12c+.
-- MySQL and MariaDB do NOT accept it - they need LIMIT 20 OFFSET 40.
SELECT id, first_name, salary
FROM   employees
ORDER BY salary DESC, id ASC
OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY;

There is no single paging syntax every product accepts, so this is one of the few places where portable SQL is genuinely impossible and the query has to be written per dialect.

Why OFFSET gets slow, and what to do

OFFSET 100000 makes the database produce 100,020 rows and throw 100,000 of them away. On page 5,000 of a report that is genuinely expensive. Keyset pagination (also called seek pagination) avoids it by remembering where the last page ended:

-- Page 1
SELECT id, order_date, total
FROM   orders
ORDER BY order_date DESC, id DESC
LIMIT 20;

-- Next page: continue after the last row seen (2024-03-19, id 1002)
SELECT id, order_date, total
FROM   orders
WHERE  (order_date, id) < ('2024-03-19', 1002)
ORDER BY order_date DESC, id DESC
LIMIT 20;

Important rules

• LIMIT without ORDER BY is meaningless. "The first 10 rows" of an unordered set is whatever the engine happened to produce.
• Always include a unique tie breaker column in ORDER BY. Without it, two rows with the same sort value can swap between pages and a row is shown twice or never.
• NULL ordering differs: MySQL and SQLite sort NULL first ascending; PostgreSQL and Oracle sort it last. Use NULLS FIRST or NULLS LAST where supported.
• Sorting by column position (ORDER BY 2) works but breaks the moment the select list changes. Avoid it.
• Text sorting follows the column's collation, so case sensitivity and accent handling are configuration, not SQL.

Common mistakes

• Paginating without a deterministic ORDER BY and getting duplicate or missing rows across pages.
• Assuming ORDER BY dept_id, salary DESC sorts both descending. It does not; dept_id is ascending.
• Using deep OFFSET values on large tables and blaming the database for being slow.
• Sorting a date stored as text and getting alphabetical order.

Best practices

• Make the sort deterministic: end every ORDER BY used for paging with the primary key.
• Support the sort with an index whose column order matches the ORDER BY, so no sort step is needed.
• Use keyset pagination for infinite scroll and for any list that goes deep.
• Decide NULL placement explicitly instead of inheriting the dialect default.

Practice

1. List employees sorted by department ascending and hire date descending.
2. Return rows 21 to 30 of orders sorted by total descending, in both MySQL and standard SQL syntax.
3. Rewrite that query as keyset pagination and explain what the client must remember between pages.