After this lesson you can
- Sort a result set on one or more columns
- Control where NULLs land in the order
- Explain why LIMIT without ORDER BY is not reproducible
A table is a set of rows. Sets have no order. If you do not write
ORDER BY, the database is free to return rows in whatever order was
cheapest today, and that can change when the data grows or the plan
changes.
SELECT name, credit
FROM customers
ORDER BY credit DESC, name ASC;
Sorting on two columns means: by credit, highest first, and where two rows tie, by name. Without the tiebreak the order of tied rows is undefined.
Try it
CREATE TABLE customers ( id int PRIMARY KEY, name text NOT NULL, country text NOT NULL, credit int);INSERT INTO customers VALUES (1, 'Nino Beridze', 'GE', 500), (2, 'Ana Kapanadze', 'GE', 1200), (3, 'Luka Meladze', 'DE', 0), (4, 'Mari Tsiklauri', 'PL', 300), (5, 'Giorgi Abashidze', 'GE', NULL);SELECT name, creditFROM customersORDER BY credit DESC NULLS LAST, idLIMIT 3;Where NULLs go
Postgres sorts NULL last when ascending and first when descending. Other
databases disagree, which is exactly the kind of difference that bites on a
migration. Say what you mean:
ORDER BY credit DESC NULLS LAST
LIMIT without ORDER BY
LIMIT 10 on its own means "any ten rows". It will look stable in
development and stop being stable in production. If you are paginating, the
order has to be deterministic, which usually means ending the sort on
something unique such as the primary key.
Try it yourself
2 visible tests · 2 hidden testsTable customers(id, name, credit), where credit may be NULL.
Return name and credit for the three customers with the highest
credit, highest first. Where two customers tie, break the tie by name
ascending. A NULL credit counts as the lowest, not the highest.
CREATE TABLE customers ( id int PRIMARY KEY, name text NOT NULL, credit int);INSERT INTO customers VALUES (1, 'Nino', 500), (2, 'Ana', 1200), (3, 'Luka', 0), (4, 'Mari', 300), (5, 'Giorgi', NULL);Sign up to check the hidden tests and save your progress. Sign up