ORDER BY, LIMIT & OFFSET

ORDER BY — sort the result

SELECT name, age FROM users
ORDER BY age;        -- ASC by default

Reverse with DESC:

ORDER BY age DESC;

ASC and DESC apply per column:

SELECT name, country, age
FROM   users
ORDER  BY country ASC, age DESC;
-- group by country alphabetically, then oldest first within each

You can sort by columns not in the SELECT list:

SELECT name FROM users ORDER BY created_at DESC;

Sort by an expression or alias

Unlike WHERE, ORDER BY runs after SELECT, so SELECT aliases work here:

SELECT name, price * 1.18 AS gross
FROM   products
ORDER  BY gross DESC;          -- ✅ alias works

You can also sort by raw expressions:

ORDER BY LENGTH(name) DESC;
ORDER BY EXTRACT(YEAR FROM created_at);

Or by column position (1-based) — handy for ad-hoc analysis but fragile in production:

SELECT name, age FROM users ORDER BY 2 DESC;   -- sort by age

NULLS FIRST / NULLS LAST

Postgres / SQLite / Oracle let you choose where NULLs land:

SELECT * FROM users ORDER BY phone NULLS LAST;
SELECT * FROM users ORDER BY ranking DESC NULLS LAST;

Defaults:

Engine	Default for ASC	Default for DESC
Postgres / Oracle	NULLS LAST	NULLS FIRST
MySQL / SQLite	NULLS FIRST	NULLS LAST

If you care about NULL placement, be explicit — it varies by engine.

LIMIT — cap the rows

SELECT * FROM products
ORDER  BY rating DESC
LIMIT  10;

LIMIT is the standard syntax in Postgres / MySQL / SQLite. SQL Server uses TOP:

-- SQL Server / T-SQL
SELECT TOP 10 * FROM products ORDER BY rating DESC;

Always pair LIMIT with ORDER BY — without sorting, "the first 10" is undefined.

OFFSET — skip rows (pagination)

SELECT * FROM products
ORDER  BY id
LIMIT  10 OFFSET 20;

This skips the first 20 rows and returns the next 10 — i.e. page 3 of 10-per-page lists. Formula:

OFFSET = (page - 1) * page_size

Some dialects also accept the standard form:

... ORDER BY id
    OFFSET 20 ROWS
    FETCH NEXT 10 ROWS ONLY;

The OFFSET-pagination problem

SELECT * FROM big_table ORDER BY id LIMIT 50 OFFSET 1000000;

The database still has to scan and discard the first million rows. Slow for large tables.

The pro fix is keyset pagination — remember the last id you saw:

-- first page
SELECT * FROM products ORDER BY id LIMIT 50;
-- next page (last_id was the highest id from previous page)
SELECT * FROM products
WHERE  id > :last_id
ORDER  BY id
LIMIT  50;

Constant-time regardless of how deep the user scrolls. Worth knowing the moment you build infinite-scroll UIs.

Sort stability — break ties

ORDER BY is not stable unless you make it so. If many rows share the same sort value, the engine can return them in any order — which means your pagination can show the same row on page 2 and page 3.

Always add a unique tie-breaker (usually the primary key):

SELECT * FROM products
ORDER  BY rating DESC, id ASC
LIMIT  10 OFFSET 10;

Sorting strings

Default string ordering depends on the collation of the column:

• ASCII / binary collations: 'B' < 'a' (uppercase first).
• Locale-aware: 'a' < 'B', 'á' next to 'a'.

If you need consistent locale-independent ordering, normalise:

ORDER BY LOWER(name);

(But that defeats the index — fine for small tables, expensive on big ones.)

Random sampling

-- Postgres / SQLite
SELECT * FROM users ORDER BY RANDOM() LIMIT 5;
-- MySQL
SELECT * FROM users ORDER BY RAND() LIMIT 5;

Cute for prototypes, slow on large tables (every row gets a random number, then a sort). Real-world solutions use TABLESAMPLE or sample by id range.

Recap

SELECT id, name, rating
FROM   products
WHERE  in_stock = TRUE
ORDER  BY rating DESC NULLS LAST,
         id     ASC               -- tie-breaker
LIMIT  20 OFFSET 40;              -- page 3, 20 per page

That's the shape of every "leaderboard / top-N / paginated list" query you'll ever write.