ORDER BY and LIMIT

The idea

Ask "what are our five most expensive products?" and you need two things: sort the products by price, highest first, and keep only the first five. That's ORDER BY and LIMIT.

Here's something that surprises most beginners: without ORDER BY, a database promises no order at all. The rows often come back in the order they were inserted, but that's luck, not a rule. After an update or on a bigger table, the order can change. If the order matters, say so.

Sorting

Tables in a database have no guaranteed order. The rows come back in whatever order is convenient for the database, which might change from one day to the next. If the order matters to your question, and it usually does, you have to ask for it with ORDER BY and the column to sort on. Here are the products from cheapest to most expensive:

SELECT name, price
FROM products
ORDER BY price;

 name                 | price
----------------------+--------
 Gel Pen Set          |  38000
 Notebook A5 Dotted   |  45000
 Ceramic Mug          |  55000
 Bamboo Plant         |  60000
 Robusta Lampung 250g |  65000
...
(15 rows)

The default direction is ascending (ASC): smallest first, A before Z, oldest date first. Add DESC for descending:

SELECT name, price
FROM products
ORDER BY price DESC;

 name                 | price
----------------------+--------
 Pour-over Kettle     | 320000
 USB-C Hub            | 289000
 Desk Lamp LED        | 245000
 French Press 600ml   | 185000
 Wireless Mouse       | 175000
...
(15 rows)

Sorting by several columns

List several columns, separated by commas. The second column only decides the order when the first is tied:

SELECT name, category, price
FROM products
ORDER BY category, price DESC;

 name                 | category    | price
----------------------+-------------+--------
 Toraja Kalosi 250g   | Coffee      | 110000
 Arabica Gayo 250g    | Coffee      |  95000
 Robusta Lampung 250g | Coffee      |  65000
 USB-C Hub            | Electronics | 289000
 Desk Lamp LED        | Electronics | 245000
 Wireless Mouse       | Electronics | 175000
 Throw Pillow         | Home        | 120000
 Scented Candle       | Home        |  75000
 Bamboo Plant         | Home        |  60000
 Pour-over Kettle     | Kitchen     | 320000
 French Press 600ml   | Kitchen     | 185000
 Ceramic Mug          | Kitchen     |  55000
 Desk Organizer       | Stationery  |  89000
 Notebook A5 Dotted   | Stationery  |  45000
 Gel Pen Set          | Stationery  |  38000
(15 rows)

Read it as: "group by category alphabetically, and within each category, most expensive first". Each column gets its own direction: category is ascending (the default) and price is descending.

You can sort by a column you don't select, too: SELECT name FROM products ORDER BY price; works fine.

LIMIT: the top N

Back to the question from the start: the three most expensive products. Sorting by price, highest first, puts them at the top, but you'd still get all 15 rows back. LIMIT n keeps only the first n rows, and it's applied after sorting, so you get the top three:

SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 3;

 name             | price
------------------+--------
 Pour-over Kettle | 320000
 USB-C Hub        | 289000
 Desk Lamp LED    | 245000
(3 rows)

The tie trap

Which customers ordered most recently? Several orders share a date:

SELECT order_id, order_date
FROM orders
ORDER BY order_date DESC
LIMIT 4;

 order_id | order_date
----------+------------
       40 | 2024-06-29
       39 | 2024-06-25
       38 | 2024-06-25
       37 | 2024-06-24
(4 rows)

Orders 38 and 39 were both placed on 25 June. With LIMIT 2, which one makes the cut? The database is free to pick either, and it may pick differently next time. A report that changes when nobody changed the data is a nightmare to debug.

The fix is a tie-breaker: add a column that's unique, usually the primary key:

SELECT order_id, order_date
FROM orders
ORDER BY order_date DESC, order_id DESC
LIMIT 2;

 order_id | order_date
----------+------------
       40 | 2024-06-29
       39 | 2024-06-25
(2 rows)

Now the order is fully decided: among orders on the same date, the higher order_id wins, every time.

Where NULLs go

stock is NULL for the Bamboo Plant. In PostgreSQL, NULLs sort as if they were larger than every value: last in ascending order, first in descending order. That's often not what you want:

SELECT name, stock
FROM products
ORDER BY stock DESC
LIMIT 3;

 name               | stock
--------------------+-------
 Bamboo Plant       |  NULL
 Notebook A5 Dotted |   200
 Ceramic Mug        |   150
(3 rows)

The plant with unknown stock isn't the best-stocked product. Say where NULLs belong with NULLS LAST (or NULLS FIRST):

SELECT name, stock
FROM products
ORDER BY stock DESC NULLS LAST
LIMIT 3;

 name                 | stock
----------------------+-------
 Notebook A5 Dotted   |   200
 Ceramic Mug          |   150
 Robusta Lampung 250g |   120
(3 rows)

OFFSET: paging through results

Websites show results in pages: rows 1 to 10, then 11 to 20. OFFSET n skips the first n rows:

SELECT order_id, order_date
FROM orders
ORDER BY order_date, order_id
LIMIT 5 OFFSET 5;

 order_id | order_date
----------+------------
        6 | 2024-01-20
        7 | 2024-01-20
        8 | 2024-02-03
        9 | 2024-02-14
       10 | 2024-02-14
(5 rows)

That's page 2 with 5 rows per page. Page p is LIMIT 5 OFFSET (p - 1) * 5. Paging only makes sense with a fully decided order (note the order_id tie-breaker), or rows can appear on two pages or none.

Clause order

You write the clauses in this order, and the database runs them in this order:

Filter first, then pick columns, then sort, then cut. Writing them in a different order (ORDER BY before WHERE, say) is a syntax error.

Why this matters for clean code

"Works on my machine, gives different numbers in production" is often an order problem. Being explicit about order, with a tie-breaker on a unique column and NULLS LAST where it matters, makes your queries deterministic: the same data always gives the same result. That's the foundation of reports people can trust.

Recap

• No ORDER BY means no guaranteed order.
• ASC is the default; DESC reverses. Each sort column has its own direction.
• Later sort columns only break ties in earlier ones.
• LIMIT n keeps the first n rows after sorting; OFFSET n skips rows for paging.
• Add a unique tie-breaker column so results are deterministic.
• NULLs sort last in ASC and first in DESC; control them with NULLS LAST / NULLS FIRST.