Sorting and limiting results

Top-N lists, paging and any result shown to a human need a defined order.

Without ORDER BY the database returns rows in whatever order is cheapest, and that order can change when data or plans change. If order matters, ask for it:

SELECT name, price FROM products ORDER BY price DESC;

ASC (the default) sorts ascending, DESC descending. List several keys to break ties: ORDER BY city, name sorts by city, then by name within each city. You can sort by an expression or by a select-list alias, because ORDER BY runs after SELECT.

Where NULLs go differs by database. PostgreSQL and Oracle treat NULL as larger than every value, so NULLs come last in ascending order and first in descending; MySQL, SQLite and SQL Server do the opposite. Be explicit with NULLS FIRST or NULLS LAST (supported by PostgreSQL, Oracle and SQLite).

To take only the first rows, PostgreSQL, MySQL and SQLite use LIMIT n, with OFFSET m to skip rows. The SQL standard spelling is FETCH FIRST n ROWS ONLY, which PostgreSQL, Oracle and SQL Server (after OFFSET ... ROWS) accept; older SQL Server code uses SELECT TOP n.

Two rules for limits:

Always combine LIMIT with an ORDER BY that is unique. If two rows tie, which one you get is arbitrary, and paging can show a row twice or skip it. Add the primary key as a final tie-breaker.
OFFSET 100000 still reads and discards 100,000 rows. For deep paging, use keyset pagination: remember the last row's sort key and ask for WHERE (price, id) < (49.00, 1) ORDER BY price DESC, id DESC LIMIT 20.

Example

SELECT name, price FROM products ORDER BY price DESC LIMIT 2;
--  Monitor  | 189.00
--  Keyboard |  49.00

-- Standard syntax, same result
SELECT name, price FROM products
ORDER BY price DESC
FETCH FIRST 2 ROWS ONLY;

-- NULL placement made explicit; id breaks ties deterministically
SELECT name, city FROM customers ORDER BY city NULLS FIRST, id;
--  Dev   | NULL
--  Ben   | London
--  Chloe | London
--  Ana   | Yerevan

-- Page 2 of orders, 2 per page, newest first
SELECT id, ordered_at FROM orders
ORDER BY ordered_at DESC, id DESC
LIMIT 2 OFFSET 2;
--  103 | 2026-02-11
--  102 | 2026-02-10

Remember

Without ORDER BY, row order is undefined
Make the sort key unique (add the primary key) before using LIMIT
NULL placement differs across databases; say NULLS FIRST/LAST
Deep OFFSET is slow; keyset pagination scales

Watch out for

Paging with LIMIT/OFFSET over a non-unique sort key, so rows repeat or vanish between pages.

Check yourself

In PostgreSQL, where do NULL cities appear in ORDER BY city ASC?
Why can SELECT ... ORDER BY city LIMIT 1 return different customers on different runs?

Official docs: www.postgresql.org/docs/current/queries-limit.html

Example

Remember

Watch out for

Check yourself

Builds on

Unlocks