Sorting & Limiting

Without ORDER BY, There Is No Order

Start with the rule that most tutorials skip: a query without ORDER BY has no guaranteed row order. Not "insertion order", not "id order" — no order at all. The engine is free to return rows in whatever sequence its chosen plan produced them.

It is easy to be fooled here, because on a small table a simple SELECT * usually does come back in id order, and it does so consistently enough that you start relying on it. Then the table grows, or an index gets used, or the same query runs against a different server, and the order changes. Nothing errors; a leaderboard is just suddenly in the wrong sequence. If the order of rows matters to your program in any way, say so with ORDER BY.

The clause itself is straightforward. ASC sorts ascending — smallest to largest, A to Z, oldest to newest — and is the default, so it is usually left out. DESC sorts the other way. Text sorts according to the column's collation, which is why sorting behaviour can differ between two servers holding identical data.

-- No ORDER BY: the order you see is not promised
SELECT name, marks FROM students;

-- Ascending is the default; these two are identical
SELECT name, marks FROM students ORDER BY marks;
SELECT name, marks FROM students ORDER BY marks ASC;

-- Highest marks first
SELECT name, marks FROM students ORDER BY marks DESC;

-- Sorting by text
SELECT name FROM students ORDER BY name ASC;

-- Sorting by a date, newest first
SELECT name, joined_on FROM students ORDER BY joined_on DESC;

Several Columns, Expressions, and Breaking Ties

List more than one column and the database sorts by the first, then uses the second only to break ties within equal values of the first, and so on. Each column gets its own direction, so ORDER BY branch ASC, marks DESC groups students by branch alphabetically and, inside each branch, puts the highest marks first.

Ties are worth thinking about whenever you also use LIMIT. If three students all scored 88 and you ask for the top two, which two you get is genuinely undefined and may differ between runs. Adding a final tiebreaker on a unique column — ORDER BY marks DESC, id ASC — makes the result deterministic, which matters enormously for pagination and for automated tests.

You can also sort by an expression rather than a plain column, and by an alias, because ORDER BY runs after SELECT. A CASE expression is the standard way to impose a custom order that is neither alphabetical nor numeric — for example putting order statuses in workflow sequence rather than in alphabetical order.

-- Branch alphabetically, and within each branch the best marks first
SELECT name, branch, marks
FROM students
ORDER BY branch ASC, marks DESC;

-- Deterministic top 3: id breaks any tie on marks
SELECT name, marks
FROM students
ORDER BY marks DESC, id ASC
LIMIT 3;

-- Sorting by an alias works, because ORDER BY runs after SELECT
SELECT name, marks * 0.6 AS weighted
FROM students
ORDER BY weighted DESC;

-- A custom order that is neither alphabetical nor numeric
SELECT id, status
FROM orders
ORDER BY CASE status
    WHEN 'pending'    THEN 1
    WHEN 'processing' THEN 2
    WHEN 'shipped'    THEN 3
    WHEN 'delivered'  THEN 4
    ELSE 5
END;

Where NULLs End Up

Sorting a column that contains NULLs raises an awkward question: is "unknown" smaller or larger than 40? There is no correct answer, so the SQL standard leaves it to the implementation — and the implementations disagree. This is a real portability difference, not a footnote.

In MySQL, NULLs sort first in ascending order and last in descending order; they behave as though they were the smallest possible value. In PostgreSQL and Oracle the default is the opposite: NULLs sort last ascending and first descending, as though they were the largest. SQL Server behaves like MySQL. So the same query, on the same data, puts unmarked students at opposite ends of the list on two different servers.

PostgreSQL and Oracle let you state your intent with NULLS FIRST or NULLS LAST. MySQL and SQL Server do not support that clause, so there you force it with an extra sort key: sort first on whether the value is NULL, then on the value itself. The expression marks IS NULL yields 1 for NULL and 0 otherwise, so ordering by it ascending puts the real values first.

-- MySQL: unmarked students appear at the TOP with plain ascending order
SELECT name, marks FROM students ORDER BY marks ASC;

-- Force NULLs to the end in MySQL (and SQL Server, with ISNULL/CASE)
SELECT name, marks
FROM students
ORDER BY (marks IS NULL) ASC, marks ASC;

-- Force NULLs to the front in MySQL
SELECT name, marks
FROM students
ORDER BY (marks IS NULL) DESC, marks ASC;

-- PostgreSQL and Oracle say it directly
-- SELECT name, marks FROM students ORDER BY marks ASC NULLS LAST;
-- SELECT name, marks FROM students ORDER BY marks DESC NULLS LAST;

LIMIT, OFFSET and Pagination

LIMIT caps how many rows come back. OFFSET skips rows before it starts counting. Together they produce pages: page 1 is LIMIT 10 OFFSET 0, page 2 is LIMIT 10 OFFSET 10, and in general the offset is (page - 1) * page_size.

Pagination is not only about screen space. Fetching an entire table of a hundred thousand rows to display twenty of them wastes database time, network bandwidth and your application server's memory, all at once. Any list that can grow should be paginated from the day it is written.

This is another area where the dialects split badly. MySQL, PostgreSQL and SQLite use LIMIT ... OFFSET .... SQL Server historically used SELECT TOP n, and from SQL Server 2012 also supports the standard OFFSET ... FETCH. Oracle 12c and later supports FETCH FIRST n ROWS ONLY. The OFFSET ... FETCH FIRST form is the actual SQL standard and works on PostgreSQL, SQL Server and Oracle, but not on MySQL — so there is no single spelling that covers everything.

• MySQL, PostgreSQL, SQLite — LIMIT 10 OFFSET 20
• MySQL shorthand — LIMIT 20, 10 means offset 20, limit 10 (the order is reversed, which is easy to get wrong)
• SQL Server — SELECT TOP 10 ..., or ORDER BY id OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY
• Oracle 12c+ — ORDER BY id OFFSET 20 ROWS FETCH FIRST 10 ROWS ONLY
• Standard SQL — OFFSET 20 ROWS FETCH FIRST 10 ROWS ONLY, which MySQL does not accept

-- First five rows
SELECT name, marks FROM students ORDER BY id LIMIT 5;

-- Top three scorers, ties broken so the result is repeatable
SELECT name, marks FROM students ORDER BY marks DESC, id ASC LIMIT 3;

-- Page 1, 2 and 3 with ten per page
SELECT name FROM students ORDER BY id LIMIT 10 OFFSET 0;
SELECT name FROM students ORDER BY id LIMIT 10 OFFSET 10;
SELECT name FROM students ORDER BY id LIMIT 10 OFFSET 20;

-- How many pages will there be?
SELECT CEIL(COUNT(*) / 10.0) AS total_pages FROM students;

-- SQL Server
-- SELECT TOP 5 name, marks FROM students ORDER BY marks DESC;

-- SQL Server 2012+, Oracle 12c+ and PostgreSQL (standard form)
-- SELECT name FROM students ORDER BY id
-- OFFSET 20 ROWS FETCH FIRST 10 ROWS ONLY;

The Two Pagination Bugs Everybody Ships Once

The first bug is pagination without a deterministic sort. LIMIT 10 OFFSET 10 on a query whose ordering is ambiguous can return a row that already appeared on page 1 and skip another entirely, because the engine is free to break ties differently each time. Users report "a record is missing" and the query looks perfectly correct in isolation. The fix is the tiebreaker from earlier: always end ORDER BY with a unique column.

The second bug is that OFFSET gets slower the deeper you go. OFFSET 100000 does not let the database skip ahead — it must produce the first hundred thousand rows in order and then throw them away. Page 1 is instant and page 5,000 crawls, on the same query.

The fix for deep pages is keyset pagination, sometimes called cursor pagination: instead of counting rows to skip, remember the last value you showed and ask for rows after it. Every page then costs the same, because the database jumps straight to that point using an index. The trade-off is that you can no longer jump to an arbitrary page number — which is exactly why infinite-scroll feeds work this way and numbered page lists do not.

-- Bug 1: ambiguous order, so pages can repeat or drop rows
-- SELECT name FROM students ORDER BY marks DESC LIMIT 10 OFFSET 10;

-- Fixed: a unique tiebreaker makes the order total
SELECT id, name, marks
FROM students
ORDER BY marks DESC, id ASC
LIMIT 10 OFFSET 10;

-- Bug 2: deep offsets do real work before discarding it
-- SELECT id, name FROM students ORDER BY id LIMIT 10 OFFSET 100000;

-- Keyset pagination: pass the last id you displayed back in
-- page 1
SELECT id, name FROM students ORDER BY id LIMIT 10;
-- page 2, where 4711 was the last id shown on page 1
SELECT id, name FROM students WHERE id > 4711 ORDER BY id LIMIT 10;
-- page 3, where 4735 was the last id shown on page 2
SELECT id, name FROM students WHERE id > 4735 ORDER BY id LIMIT 10;