Skip to content

SQL lesson 3 of 8

Sorting and Limiting SQL Results: ORDER BY, LIMIT, DISTINCT

Put SQL rows in a deliberate order with ORDER BY, take just the first few with LIMIT and OFFSET, and remove repeated rows with DISTINCT — including where NULLs and capital letters end up.

Published · Every example on this page was run before it was published.

A table has no order. That sentence surprises people, because a small result usually comes back in insertion order and it is tempting to conclude that it always will. It will not: the order depends on how the database decided to find the rows, and that can change when an index is added, when the table grows, or when a filter changes. If you care what order rows arrive in, you have to say so, and ORDER BY is how you say it.

The same is true of "the top three" and "the cheapest one" — those are questions about order, so they need ORDER BY before LIMIT means anything. Every example below builds its own table, because each run in this playground starts from an empty database.

ORDER BY

ORDER BY goes after WHERE, and names the column to sort on. ASC (ascending, the default) sorts smallest-first; DESC sorts largest-first.

SQL
CREATE TABLE tools (id INTEGER PRIMARY KEY, name TEXT, shelf TEXT, deposit INTEGER);
INSERT INTO tools (name, shelf, deposit) VALUES
  ('hedge trimmer', 'garden', 950),
  ('cordless drill', 'power', 1200),
  ('wheelbarrow', 'garden', 500),
  ('tile cutter', 'power', 1500),
  ('hand saw', 'hand', 350);

SELECT name, deposit FROM tools ORDER BY deposit;

SELECT name, deposit FROM tools ORDER BY deposit DESC;
Output
name           | deposit
---------------+--------
hand saw       |     350
wheelbarrow    |     500
hedge trimmer  |     950
cordless drill |    1200
tile cutter    |    1500
(5 rows)

name           | deposit
---------------+--------
tile cutter    |    1500
cordless drill |    1200
hedge trimmer  |     950
wheelbarrow    |     500
hand saw       |     350
(5 rows)

Sorting on text sorts alphabetically, and here is the first thing worth checking rather than assuming:

SQL
CREATE TABLE tools (id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO tools (name) VALUES ('wheelbarrow'), ('Hammer'), ('axe'), ('Bench vice');

SELECT name FROM tools ORDER BY name;

SELECT name FROM tools ORDER BY name COLLATE NOCASE;
Output
name
-----------
Bench vice
Hammer
axe
wheelbarrow
(4 rows)

name
-----------
axe
Bench vice
Hammer
wheelbarrow
(4 rows)

The default sort compares the underlying character codes, and every capital letter comes before every lowercase one, so Bench vice and Hammer are dragged above axe in the first result. COLLATE NOCASE gives you the alphabetical order a human expects. PostgreSQL and MySQL reach the same result with different spellings, so ORDER BY lower(name) is the portable habit — it works everywhere.

Sorting on more than one column

Name several sort keys separated by commas. The second is only consulted when the first is tied, the third only when the first two are, and DESC applies to the one key it follows — not to the rest.

SQL
CREATE TABLE tools (id INTEGER PRIMARY KEY, name TEXT, shelf TEXT, deposit INTEGER);
INSERT INTO tools (name, shelf, deposit) VALUES
  ('hedge trimmer', 'garden', 950),
  ('cordless drill', 'power', 1200),
  ('wheelbarrow', 'garden', 500),
  ('tile cutter', 'power', 1500),
  ('hand saw', 'hand', 350),
  ('post driver', 'garden', 1500);

SELECT shelf, name, deposit
FROM tools
ORDER BY shelf ASC, deposit DESC;
Output
shelf  | name           | deposit
-------+----------------+--------
garden | post driver    |    1500
garden | hedge trimmer  |     950
garden | wheelbarrow    |     500
hand   | hand saw       |     350
power  | tile cutter    |    1500
power  | cordless drill |    1200
(6 rows)

Shelves come out alphabetically, and inside each shelf the dearest tool is first. Ties are why the second key exists: the two 1500 deposits sit on different shelves here, but if two rows tie on every key you name, the order between them is still undefined. Add a final tie-breaker on something unique — usually the primary key — when a stable order matters.

You can also sort by something that is not in the SELECT list at all, by an alias you defined, or by an expression:

SQL
CREATE TABLE tools (id INTEGER PRIMARY KEY, name TEXT, deposit INTEGER);
INSERT INTO tools (name, deposit) VALUES
  ('hedge trimmer', 950),
  ('cordless drill', 1200),
  ('hand saw', 350);

SELECT name, deposit / 100.0 AS pounds
FROM tools
ORDER BY pounds DESC;

SELECT name FROM tools ORDER BY length(name);
Output
name           | pounds
---------------+-------
cordless drill |     12
hedge trimmer  |    9.5
hand saw       |    3.5
(3 rows)

name
--------------
hand saw
hedge trimmer
cordless drill
(3 rows)

ORDER BY runs after the SELECT list has been worked out, which is why an alias is allowed there but not in WHERE. Sorting by a column you did not select is legal too, and occasionally the clearest thing to do — though a reader then cannot see why the rows are in that order, so a comment earns its place.

Where NULL sorts

NULL is not a value, so it needs a rule of its own. SQLite's rule is that NULL sorts before every real value ascending, and after them descending.

SQL
CREATE TABLE tools (id INTEGER PRIMARY KEY, name TEXT, deposit INTEGER);
INSERT INTO tools (name, deposit) VALUES
  ('hedge trimmer', 950),
  ('donated ladder', NULL),
  ('hand saw', 350);

SELECT name, deposit FROM tools ORDER BY deposit;

SELECT name, deposit FROM tools ORDER BY deposit NULLS LAST;
Output
name           | deposit
---------------+--------
donated ladder | NULL
hand saw       |     350
hedge trimmer  |     950
(3 rows)

name           | deposit
---------------+--------
hand saw       |     350
hedge trimmer  |     950
donated ladder | NULL
(3 rows)

NULLS LAST says it explicitly, and SQLite has understood it since version 3.30 — PostgreSQL does too, but MySQL does not accept that syntax at all. The portable trick is to sort on the test first: ORDER BY deposit IS NULL, deposit, which sorts the 0s (not null) before the 1s (null) and then sorts within each group.

LIMIT and OFFSET

LIMIT n returns at most n rows. OFFSET k skips the first k before it starts counting, which is how page two of a listing is fetched.

SQL
CREATE TABLE tools (id INTEGER PRIMARY KEY, name TEXT, deposit INTEGER);
INSERT INTO tools (name, deposit) VALUES
  ('hedge trimmer', 950),
  ('cordless drill', 1200),
  ('wheelbarrow', 500),
  ('tile cutter', 1500),
  ('hand saw', 350),
  ('post driver', 1100);

SELECT name, deposit FROM tools ORDER BY deposit DESC LIMIT 3;

SELECT name, deposit FROM tools ORDER BY deposit DESC LIMIT 3 OFFSET 3;
Output
name           | deposit
---------------+--------
tile cutter    |    1500
cordless drill |    1200
post driver    |    1100
(3 rows)

name          | deposit
--------------+--------
hedge trimmer |     950
wheelbarrow   |     500
hand saw      |     350
(3 rows)

That is a "top three" and a "next three", and both are only meaningful because of the ORDER BY. A LIMIT with no ORDER BY gives you three rows the database found first, which is not the same as three particular rows and may differ between runs.

LIMIT and OFFSET are spelled this way in SQLite, PostgreSQL and MySQL. Microsoft SQL Server uses TOP n or OFFSET ... FETCH NEXT ... ROWS ONLY instead, so this is one clause to check when moving a query between engines.

DISTINCT

DISTINCT removes repeated rows from the result. The crucial word is rows: it looks at the whole SELECT list together, not at one column.

SQL
CREATE TABLE tools (id INTEGER PRIMARY KEY, name TEXT, shelf TEXT, condition TEXT);
INSERT INTO tools (name, shelf, condition) VALUES
  ('hedge trimmer', 'garden', 'good'),
  ('cordless drill', 'power', 'good'),
  ('wheelbarrow', 'garden', 'worn'),
  ('tile cutter', 'power', 'good'),
  ('hand saw', 'hand', 'worn');

SELECT DISTINCT shelf FROM tools ORDER BY shelf;

SELECT DISTINCT shelf, condition FROM tools ORDER BY shelf, condition;
Output
shelf
------
garden
hand
power
(3 rows)

shelf  | condition
-------+----------
garden | good
garden | worn
hand   | worn
power  | good
(4 rows)

The first query answers "which shelves do we use?" and gives three rows from five. The second keeps every distinct pair, so garden appears twice — once with good, once with worn. Adding a column to a DISTINCT query can only ever increase the number of rows it returns, which trips people up when they add an id column "just to see" and every duplicate reappears.

DISTINCT also treats all NULLs as equal to each other, collapsing them into one row — the opposite of how NULL behaves in WHERE. It is a deliberate exception, and the same in every database.

A worked example

A small reading list, sorted and paged:

SQL
CREATE TABLE borrowed (
  id        INTEGER PRIMARY KEY,
  title     TEXT,
  section   TEXT,
  days_out  INTEGER
);

INSERT INTO borrowed (title, section, days_out) VALUES
  ('Rust Never Sleeps',   'repair',   12),
  ('The Quiet Kiln',      'craft',    3),
  ('Mending Bicycles',    'repair',   31),
  ('Bread Without Fuss',  'kitchen',  7),
  ('Kiln to Table',       'craft',    31),
  ('Sharpening by Hand',  'repair',   NULL),
  ('Soup for a Crowd',    'kitchen',  19);

SELECT DISTINCT section FROM borrowed ORDER BY section;

SELECT title, section, days_out
FROM borrowed
WHERE days_out IS NOT NULL
ORDER BY days_out DESC, title ASC
LIMIT 4;
Output
section
-------
craft
kitchen
repair
(3 rows)

title             | section | days_out
------------------+---------+---------
Kiln to Table     | craft   |       31
Mending Bicycles  | repair  |       31
Soup for a Crowd  | kitchen |       19
Rust Never Sleeps | repair  |       12
(4 rows)

Three sections out of seven rows in the first table. In the second, the filter drops the row with no recorded loan length, days_out DESC puts the longest loans first, title ASC breaks the tie between the two 31-day loans alphabetically, and LIMIT 4 cuts the list at four. Swap the tie-breaker to title DESC and only the two 31-day rows change places — a good way to prove to yourself that the second key really only acts on ties.

Common mistakes

Assuming the order you inserted rows in is the order you get back

SQL
CREATE TABLE tools (id INTEGER PRIMARY KEY, name TEXT, deposit INTEGER);
INSERT INTO tools (name, deposit) VALUES ('wheelbarrow', 500), ('hand saw', 350), ('tile cutter', 1500);
SELECT name FROM tools;
Output
name
-----------
wheelbarrow
hand saw
tile cutter
(3 rows)

This does come back in insertion order, and that is exactly the trap: nothing in the query asked for it. The same query against a table with an index on name, or against PostgreSQL after some rows were updated, can return a different order without anything having gone wrong. Treat any result without an ORDER BY as an unordered bag of rows, however tidy it looks today.

LIMIT before ORDER BY

SQL
CREATE TABLE tools (id INTEGER PRIMARY KEY, name TEXT, deposit INTEGER);
INSERT INTO tools (name, deposit) VALUES ('wheelbarrow', 500), ('hand saw', 350), ('tile cutter', 1500);
SELECT name, deposit FROM tools LIMIT 2 ORDER BY deposit DESC;
Output
Error in the statement on line 3: near "ORDER": syntax error

The clauses have a fixed order — FROM, WHERE, GROUP BY, HAVING, ORDER BY, then LIMIT — and putting LIMIT before ORDER BY is a syntax error rather than a reordering. Worth meeting once so the message is familiar: SQLite reports the line the whole statement starts on, line 3 here.

Expecting DISTINCT to apply to just the first column

SQL
CREATE TABLE tools (id INTEGER PRIMARY KEY, name TEXT, shelf TEXT);
INSERT INTO tools (name, shelf) VALUES
  ('hedge trimmer', 'garden'),
  ('wheelbarrow', 'garden'),
  ('hand saw', 'hand');

SELECT DISTINCT shelf, name FROM tools ORDER BY shelf, name;
Output
shelf  | name
-------+--------------
garden | hedge trimmer
garden | wheelbarrow
hand   | hand saw
(3 rows)

Three rows, from a query that was probably meant to list two shelves. DISTINCT is not a function applied to shelf; it applies to the whole row, and adding name made every row unique. To get one row per shelf and see something about the tools on it, you need grouping rather than DISTINCT — which is the next lesson.

Sorting a column that holds mixed types

SQL
CREATE TABLE readings (id INTEGER PRIMARY KEY, label TEXT, value INTEGER);
INSERT INTO readings (label, value) VALUES
  ('monday', 12),
  ('tuesday', 'not recorded'),
  ('wednesday', 3),
  ('thursday', NULL);

SELECT label, value, typeof(value) AS stored_as FROM readings ORDER BY value;
Output
label     | value        | stored_as
----------+--------------+----------
thursday  | NULL         | null
wednesday |            3 | integer
monday    |           12 | integer
tuesday   | not recorded | text
(4 rows)

SQLite lets a sentence into a column declared INTEGER unless the table was created STRICT, and when it sorts a column like that it groups by storage class first: NULL, then numbers, then text, then blobs. So 'not recorded' sorts after 12 — not because of its letters, but because all text sorts after all numbers. PostgreSQL and MySQL would have rejected the insert and you would never meet this ordering. It is worth seeing once, because it explains an otherwise baffling sort in a table someone else loaded badly.

Next steps

You can now shape a result: which rows, in what order, how many. Everything so far has returned one output row per table row, though. The next lesson is about collapsing many rows into one summary — counts, totals and averages per group — which is where SQL starts answering questions you could not answer by reading the table. Open the SQL playground and try the worked example with the LIMIT removed to see the whole ordering.

Write it yourself

Reading about code and writing it are different skills. These exercises practise exactly what this lesson covered; they run in this tab and need no account.

Open the SQL playground