ORDER BY, LIMIT and OFFSET

A table has no built-in order. The rows of a result arrive in whatever order the database finds convenient, which depends on how the data happens to be stored and on the plan the database picks for the query. The PostgreSQL and SQLite manuals both say so plainly: without ORDER BY, the order is not defined. So whenever the order matters, to a person reading a list or to a program showing “the first ten”, the query has to ask for it.

Sorting with ORDER BY

ORDER BY comes after WHERE and lists one or more sort keys. Each key sorts in ascending order (ASC, smallest first) unless you add DESC. Rows are sorted by the first key; only rows that tie on it are sorted by the second key, and so on:

CREATE TABLE products (
  id          INTEGER PRIMARY KEY,
  name        TEXT    NOT NULL,
  category    TEXT    NOT NULL,
  price_paise INTEGER NOT NULL
);
INSERT INTO products VALUES
  (1, 'Toor dal 1 kg',       'pulses',    16500),
  (2, 'Basmati rice 5 kg',   'grains',    62000),
  (3, 'Moong dal 1 kg',      'pulses',    14500),
  (4, 'Sona masoori 10 kg',  'grains',    74000),
  (5, 'Chana dal 1 kg',      'pulses',    11000),
  (6, 'Wheat atta 10 kg',    'grains',    48000),
  (7, 'Masoor dal 1 kg',     'pulses',    14500);

-- By category A to Z; inside a category, the most expensive first;
-- products with the same price by name.
SELECT category, price_paise, name
FROM products
ORDER BY category, price_paise DESC, name;

┌──────────┬─────────────┬────────────────────┐
│ category │ price_paise │        name        │
├──────────┼─────────────┼────────────────────┤
│ grains   │ 74000       │ Sona masoori 10 kg │
│ grains   │ 62000       │ Basmati rice 5 kg  │
│ grains   │ 48000       │ Wheat atta 10 kg   │
│ pulses   │ 16500       │ Toor dal 1 kg      │
│ pulses   │ 14500       │ Masoor dal 1 kg    │
│ pulses   │ 14500       │ Moong dal 1 kg     │
│ pulses   │ 11000       │ Chana dal 1 kg     │
└──────────┴─────────────┴────────────────────┘

DESC belongs to the key just before it, not to the whole list: ORDER BY category, price_paise DESC sorts the categories from A to Z and the prices inside each category from high to low. The two lentils at 14500 paise tie on both of the first two keys, and the third key, name, decides between them.

A sort key can be any expression, a column that is not in the SELECT list, or the name you gave an output column with AS. You may also see ORDER BY 2, meaning the second column of the output; it works in all three engines, but it breaks silently when someone reorders the SELECT list, and MySQL’s manual marks it as deprecated.

Ties and the tie-breaker

When several rows have the same value for every sort key, the database may return them in any order, and that order can change between engines, versions or runs. Usually nobody notices, until a query takes only the first few rows:

CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT NOT NULL, price_paise INTEGER NOT NULL);
INSERT INTO products VALUES
  (1, 'Cashews 500 g',    52000), (2, 'Saffron 1 g',    30000),
  (3, 'Almonds 500 g',    45000), (4, 'Ghee 1 l',       65000),
  (5, 'Pistachios 250 g', 30000), (6, 'Honey 500 g',    24000),
  (7, 'Raisins 500 g',    18000), (8, 'Walnuts 500 g',  52000);

-- Six rows, to see what happens at the edge of a top five:
-- the fifth and sixth products cost the same.
SELECT id, name, price_paise
FROM products
ORDER BY price_paise DESC
LIMIT 6;

-- The top five, with id as the tie-breaker: the same five rows every time.
SELECT id, name, price_paise
FROM products
ORDER BY price_paise DESC, id
LIMIT 5;

┌────┬──────────────────┬─────────────┐
│ id │       name       │ price_paise │
├────┼──────────────────┼─────────────┤
│ 4  │ Ghee 1 l         │ 65000       │
│ 1  │ Cashews 500 g    │ 52000       │
│ 8  │ Walnuts 500 g    │ 52000       │
│ 3  │ Almonds 500 g    │ 45000       │
│ 2  │ Saffron 1 g      │ 30000       │
│ 5  │ Pistachios 250 g │ 30000       │
└────┴──────────────────┴─────────────┘
┌────┬───────────────┬─────────────┐
│ id │     name      │ price_paise │
├────┼───────────────┼─────────────┤
│ 4  │ Ghee 1 l      │ 65000       │
│ 1  │ Cashews 500 g │ 52000       │
│ 8  │ Walnuts 500 g │ 52000       │
│ 3  │ Almonds 500 g │ 45000       │
│ 2  │ Saffron 1 g   │ 30000       │
└────┴───────────────┴─────────────┘

Saffron and pistachios cost the same 30000 paise, so they share fifth place. SQLite happened to put saffron first, but nothing promises that. Another engine, a new index or more data could make the same query return pistachios, and two pages of a list could then both show, or both skip, the same product. The second query adds id, which is unique, as the last key. With a tie-breaker that no two rows share, the order is complete and the top five is the same every time.

PostgreSQL can instead keep every product that ties for the last place, with the standard clause FETCH FIRST 5 ROWS WITH TIES in place of a LIMIT. Sorted by price_paise DESC alone, it returns the six rows of the first query here; after ORDER BY price_paise DESC, id nothing ties any more, and it returns five.

Where NULLs go

The engines do not agree on where missing values belong in a sort:

• SQLite and MySQL treat NULL as smaller than any value: first in ascending order, last in descending order.
• PostgreSQL treats NULL as larger than any value: last in ascending order, first in descending order.

You can choose the position yourself. Standard SQL adds NULLS FIRST or NULLS LAST after a sort key, and PostgreSQL and SQLite accept it. MySQL 8.4 does not, but sorting by price_paise IS NULL first does the same job there, because that test is 0 for the priced rows and 1 for the others:

CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT NOT NULL, price_paise INTEGER);
INSERT INTO products VALUES
  (1, 'Jaggery 1 kg', 9000),    (2, 'Kokum 200 g', NULL),  -- not priced yet
  (3, 'Tamarind 500 g', 12000), (4, 'Mango pickle 400 g', 15000);

-- SQLite's default: NULL sorts before every other value.
SELECT name, price_paise FROM products ORDER BY price_paise;

-- Standard SQL, accepted by SQLite and PostgreSQL but not by MySQL.
SELECT name, price_paise FROM products ORDER BY price_paise NULLS LAST;

-- A form that also works in MySQL: sort by "is it missing?" first (0 before 1).
SELECT name, price_paise FROM products ORDER BY price_paise IS NULL, price_paise;

┌────────────────────┬─────────────┐
│        name        │ price_paise │
├────────────────────┼─────────────┤
│ Kokum 200 g        │             │
│ Jaggery 1 kg       │ 9000        │
│ Tamarind 500 g     │ 12000       │
│ Mango pickle 400 g │ 15000       │
└────────────────────┴─────────────┘
┌────────────────────┬─────────────┐
│        name        │ price_paise │
├────────────────────┼─────────────┤
│ Jaggery 1 kg       │ 9000        │
│ Tamarind 500 g     │ 12000       │
│ Mango pickle 400 g │ 15000       │
│ Kokum 200 g        │             │
└────────────────────┴─────────────┘
┌────────────────────┬─────────────┐
│        name        │ price_paise │
├────────────────────┼─────────────┤
│ Jaggery 1 kg       │ 9000        │
│ Tamarind 500 g     │ 12000       │
│ Mango pickle 400 g │ 15000       │
│ Kokum 200 g        │             │
└────────────────────┴─────────────┘

The second and third queries give the same order; the third form is the one to use in MySQL. Writing the position of NULLs explicitly also documents the decision for the next reader.

Taking the first rows: LIMIT

LIMIT n keeps the first n rows of the sorted result, and OFFSET m skips m rows before it starts counting. The order of work matters: the database filters, then sorts, then skips, then takes.

LIMIT without ORDER BY returns an arbitrary few rows, which is fine for a quick look at a table and wrong almost everywhere else.

Paging through results

A list shown page by page is ORDER BY with LIMIT and OFFSET. With s rows a page, page n is LIMIT s OFFSET (n - 1) * s:

CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
INSERT INTO products VALUES
  (1, 'Amla'), (2, 'Bael'), (3, 'Chikoo'), (4, 'Dates'),
  (5, 'Elaichi'), (6, 'Figs'), (7, 'Guava'), (8, 'Honey');

-- Three products a page. Page 2 skips the first page's 3 rows.
SELECT id, name FROM products ORDER BY name LIMIT 3 OFFSET 3;

-- The last page holds what is left.
SELECT id, name FROM products ORDER BY name LIMIT 3 OFFSET 6;

┌────┬─────────┐
│ id │  name   │
├────┼─────────┤
│ 4  │ Dates   │
│ 5  │ Elaichi │
│ 6  │ Figs    │
└────┴─────────┘
┌────┬───────┐
│ id │ name  │
├────┼───────┤
│ 7  │ Guava │
│ 8  │ Honey │
└────┴───────┘

The first query is page 2; the last page simply holds fewer rows. SQLite and MySQL also accept a shorter form with two numbers and a comma, which is easy to misread:

CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
INSERT INTO products VALUES
  (1, 'Amla'), (2, 'Bael'), (3, 'Chikoo'), (4, 'Dates'),
  (5, 'Elaichi'), (6, 'Figs'), (7, 'Guava'), (8, 'Honey');

-- SQLite and MySQL also accept LIMIT with two numbers and a comma.
SELECT id, name FROM products ORDER BY name LIMIT 2, 3;

┌────┬─────────┐
│ id │  name   │
├────┼─────────┤
│ 3  │ Chikoo  │
│ 4  │ Dates   │
│ 5  │ Elaichi │
└────┴─────────┘

LIMIT 2, 3 skipped two rows and returned three: the first number is the offset and the second the count, the reverse of the order in which LIMIT … OFFSET … names them. The SQLite manual itself calls this counter-intuitive and recommends the OFFSET keyword, and PostgreSQL’s LIMIT has no comma form at all, so LIMIT 3 OFFSET 2 is the form to write.

Three more details differ between the engines:

• Skipping rows and returning all the rest. PostgreSQL writes LIMIT ALL OFFSET m and SQLite LIMIT -1 OFFSET m, since a negative limit means no limit there. MySQL has no keyword for it: its manual suggests a very large count, LIMIT m, 18446744073709551615.
• What the count and the offset may be. PostgreSQL and SQLite accept expressions that give a whole number. MySQL wants non-negative whole-number constants, or ? placeholders in a prepared statement.
• Rows tied for the last place. Only PostgreSQL, of the three, can add them, with FETCH FIRST n ROWS WITH TIES.

Paging with OFFSET has a cost that grows with the page number: to show page 500, the database still has to work through the rows of the 499 pages before it, only to throw them away. It also shifts when rows are added while someone pages through. For long lists, the performance module shows keyset pagination, which remembers the last row of a page instead of counting rows.

Text and numbers that sort strangely

Two sorting surprises come from the data rather than from the query:

CREATE TABLE items (name TEXT NOT NULL, size TEXT NOT NULL);
INSERT INTO items VALUES ('apple', '9'), ('Banana', '10'), ('cherry', '100'), ('Date', '25');

-- Text sorts by character codes: capital letters before small ones,
-- and '10' before '9', because '1' comes before '9'.
SELECT name, size FROM items ORDER BY name;
SELECT name, size FROM items ORDER BY size;

-- Fixes: sort without regard to case, and sort numbers as numbers.
SELECT name FROM items ORDER BY name COLLATE NOCASE;
SELECT name, size FROM items ORDER BY CAST(size AS INTEGER);

┌────────┬──────┐
│  name  │ size │
├────────┼──────┤
│ Banana │ 10   │
│ Date   │ 25   │
│ apple  │ 9    │
│ cherry │ 100  │
└────────┴──────┘
┌────────┬──────┐
│  name  │ size │
├────────┼──────┤
│ Banana │ 10   │
│ cherry │ 100  │
│ Date   │ 25   │
│ apple  │ 9    │
└────────┴──────┘
┌────────┐
│  name  │
├────────┤
│ apple  │
│ Banana │
│ cherry │
│ Date   │
└────────┘
┌────────┬──────┐
│  name  │ size │
├────────┼──────┤
│ apple  │ 9    │
│ Banana │ 10   │
│ Date   │ 25   │
│ cherry │ 100  │
└────────┴──────┘

• SQLite compares text with the BINARY collation, character code by character code, and capital letters come before small ones in that code. Banana and Date therefore sort before apple. COLLATE NOCASE sorts the 26 English letters without regard to case. MySQL sorts text without regard to case by default, and PostgreSQL sorts by the column’s collation, which by default comes from the locale chosen when the database was created, so the same query can list these four names in different orders in different engines.
• A number stored as text sorts like text: '10' comes before '9' because the character 1 comes before 9. CAST(size AS INTEGER) sorts the values as numbers. The better fix is to store numbers in a number column, which the module on data types explains.

Key takeaways

• Without ORDER BY the order of rows is undefined; ask for an order whenever it matters.
• Each sort key has its own direction, ASC by default; DESC applies only to the key before it.
• End the sort keys with a unique column, such as the primary key, so that ties cannot change the result.
• SQLite and MySQL sort NULLs first in ascending order, PostgreSQL last; use NULLS LAST or ORDER BY x IS NULL, x.
• Page n of s rows is LIMIT s OFFSET (n - 1) * s; in LIMIT a, b the first number is the offset.