ORDER BY, LIMIT and paging through results

A table is a set, so without ORDER BY the database promises nothing about the order rows come back in. It may look sorted. It may be sorted today and not next month. This lesson is how to ask for an order and how to take a page of results.

ORDER BY

SELECT title, price_paise FROM books ORDER BY price_paise DESC LIMIT 5;

              title               | price_paise
----------------------------------+-------------
 A Suitable Boy                   |       69900
 The Ministry of Utmost Happiness |       52500
 Sea of Poppies                   |       48500
 The God of Small Things          |       45000
 The Inheritance of Loss          |       44000
(5 rows)

ASC is the default and rarely written. DESC reverses it.

More than one key

SELECT shelf, title FROM books ORDER BY shelf, title LIMIT 6;

 shelf |         title
-------+------------------------
 B-01  | Gitanjali
 B-01  | The Home and the World
 B-02  | Breast Stories
 B-02  | Hajar Churashir Maa
 C-01  | A Flight of Pigeons
 C-01  | Rusty Runs Away
(6 rows)

Sort by shelf; within each shelf, sort by title. The second key only decides ties in the first.

Each key gets its own direction:

ORDER BY shelf ASC, price_paise DESC

You can order by things you did not select

SELECT title FROM books ORDER BY price_paise DESC LIMIT 3;

Legal, and occasionally confusing to read — the output gives no clue why those three. Select the column when a human will read the result.

And unlike WHERE, ORDER BY can use a SELECT alias, because it runs after SELECT:

SELECT title, price_paise / 100 AS rupees FROM books ORDER BY rupees DESC LIMIT 3;

That asymmetry is the execution order from lesson one being visible again.

NULL sorts at one end, and the default may surprise you

SELECT name, date_of_birth FROM members ORDER BY date_of_birth DESC LIMIT 5;

      name      | date_of_birth
----------------+---------------
 Yash Deshpande |
 Deepa Apte     |
 Trupti Lele    |
 Amit Ranade    | 2008-06-13
 Sandeep Patil  | 2007-12-06
(5 rows)

Three members with no date of birth came first. That is almost certainly not what somebody asking for "the youngest members" wanted.

PostgreSQL treats NULL as larger than everything, so it sorts last in ASC and first in DESC. Say what you want explicitly:

SELECT name, date_of_birth FROM members ORDER BY date_of_birth DESC NULLS LAST LIMIT 5;

     name      | date_of_birth
---------------+---------------
 Amit Ranade   | 2008-06-13
 Sandeep Patil | 2007-12-06
 Aditya Kale   | 2004-09-03
 Kavita Joshi  | 2004-08-21
 Pooja Jadhav  | 2000-12-23
(5 rows)

NULLS FIRST and NULLS LAST work with either direction. Other databases default differently — MySQL sorts NULL first in ASC — so being explicit also makes the query portable.

Text ordering is not ASCII ordering

SELECT title FROM books ORDER BY title;

Text sorts by collation, which is a locale-aware set of rules, not by byte value. In a typical en_US.UTF-8 database, case and punctuation are largely ignored for ordering, so apple sorts near Apple. In the C collation it is strict byte order and every capital letter sorts before every lowercase one.

Two consequences worth knowing now:

• The same query can order differently on two servers with different collations. If order matters for correctness, pin it: ORDER BY title COLLATE "C".
• Sorting a large text column is expensive. Module 7 shows how an index can remove the sort entirely.

LIMIT and OFFSET

SELECT title FROM books ORDER BY title LIMIT 3 OFFSET 0;

        title
---------------------
 A Flight of Pigeons
 A Suitable Boy
 An Equal Music
(3 rows)

SELECT title FROM books ORDER BY title LIMIT 3 OFFSET 3;

       title
--------------------
 Batatyachi Chal
 Breast Stories
 Clear Light of Day
(3 rows)

Page 1 and page 2. OFFSET skips, LIMIT takes.

LIMIT without ORDER BY is meaningless. "Give me any 10 rows, I do not care which" is almost never what you meant, and the rows you get can change between runs.

Two real problems with OFFSET paging

It gets slower the deeper you go. OFFSET 100000 makes the database produce a hundred thousand rows and throw them away before returning ten. Page 1 is instant; page 10,000 is not.

Rows move between pages. If somebody inserts a book while a reader is on page 2, one row shifts from page 2 to page 3 — so the reader sees it twice, or never. On a busy table this is not hypothetical.

Keyset paging, which fixes both

Instead of "skip 30", say "everything after the last one I saw":

-- first page
SELECT id, title FROM books ORDER BY title, id LIMIT 3;

-- next page: pass back the last row's sort values
SELECT id, title FROM books
WHERE (title, id) > ('An Equal Music', 17)
ORDER BY title, id
LIMIT 3;

(title, id) > (...) is a row comparison — it compares the pair in order, like sorting. It needs a tiebreaker column, which is why id is in both the ORDER BY and the comparison; without it, two books with the same title would make the boundary ambiguous.

This stays fast at any depth, because an index can jump straight to the starting point, and rows do not shift. The cost is that you cannot jump to "page 57" — which is why it suits infinite scroll and an API cursor, and not a numbered pager.

Use OFFSET for a small admin table. Use keyset paging for anything large or busy.

FETCH FIRST, the standard spelling

SELECT title FROM books ORDER BY title FETCH FIRST 3 ROWS ONLY;

Standard SQL; LIMIT is the PostgreSQL and MySQL spelling. They do the same thing, and LIMIT is what you will see everywhere.

Check your work

What order you get without ORDER BY. No promised order at all — and it can change.

What a second sort key does. Decides ties in the first.

Why ORDER BY can use an alias when WHERE cannot. It runs after SELECT.

Where PostgreSQL sorts NULL. Last in ASC, first in DESC — so a DESC "newest first" query leads with the unknowns.

How to fix that, and why it also helps portability. NULLS LAST; other databases default differently.

Why text order can differ between servers. Collation. Pin it with COLLATE "C" if order is part of correctness.

Why LIMIT without ORDER BY is meaningless. You get any rows, and possibly different ones each run.

Two problems with OFFSET. It gets slower the deeper you go, and rows shift between pages when the data changes.

What keyset paging is. "Everything after the last row I saw", compared as a row — fast at any depth, and it needs a tiebreaker column.

What keyset paging cannot do. Jump to page 57.

Practice

1. Select all books with no ORDER BY, several times. Then insert and delete a row and try again. Note whether the order changed.
2. Order books by price descending, then ascending.
3. Order by shelf then title. Then by shelf then price descending.
4. Order members by date of birth descending and explain the first three rows.
5. Add NULLS LAST and compare.
6. Order by an alias. Then try the same alias in WHERE and read the error.
7. Order titles normally, then with COLLATE "C". Find a pair that swaps.
8. Page through all 40 books three at a time with OFFSET, and count how many queries it takes.
9. On page 2, insert a book that sorts first, then fetch page 3. Find the row you now see twice.
10. Rewrite the same paging with keyset paging and repeat step 9.
11. Explain why keyset paging needs id in both the ORDER BY and the comparison.
12. Turn on \timing and compare OFFSET 0 with OFFSET 30 on this table. Then reason about what OFFSET 100000 would cost on a million rows.

Official documentation

• PostgreSQL — Sorting rows — ORDER BY, multiple keys and the NULLS options.
• PostgreSQL — LIMIT and OFFSET — Including the explicit warning that LIMIT without ORDER BY returns an unpredictable subset.
• PostgreSQL — Collation support — Why text order depends on locale.
• PostgreSQL — Row constructors — The (a, b) > (c, d) comparison keyset paging uses.

Next: NULL, which is neither zero nor empty.