Limiting Results and DISTINCT

Sometimes you do not want every row. You want the three most expensive products, or the next page of results, or just the list of countries your customers come from with no repeats. SQLite gives you two small clauses for exactly this: LIMIT caps how many rows come back, and DISTINCT throws away duplicate rows. Both are simple, and both have one trap that catches almost everyone.

This builds on Sorting with ORDER BY, where you put rows in a defined order. That order is what makes “the top few” mean something here.

Capping Rows With LIMIT

Add LIMIT n to the end of a query and SQLite returns at most n rows. Everything else about the query stays the same. Here are the first three products, capped:

SELECT name, price
FROM products
LIMIT 3;

name | price
Wireless Mouse | 24.99
Mechanical Keyboard | 89
USB-C Hub | 39.5

That works, but ask yourself: which three did you get? You did not say. SQLite handed back three rows in whatever order it found them, and that order is not guaranteed. “The first three” is only meaningful once you define what “first” means.

LIMIT without ORDER BY is nondeterministic. Without an ORDER BY, the rows you get from LIMIT 3 are undefined. SQLite may return different rows tomorrow, on a different machine, or after you insert data. If you care which rows come back, you must sort first.

Top-N Done Right: ORDER BY Then LIMIT

A real “top N” query is always ORDER BY ... LIMIT n. Sort the rows so the ones you want float to the top, then cap the count. Here are the three most expensive products:

The three most expensive products, nothing else. Which clause put them in that order, and which one cut the list to three?

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

name | price
27-inch Monitor | 219
Mechanical Keyboard | 89
Chef Knife | 54.99

Now the answer is repeatable. Sort descending by price, keep the top three, done. Flip DESC to ASC and you get the three cheapest instead. Click through a few shapes:

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

name | price
27-inch Monitor | 219
Mechanical Keyboard | 89
Chef Knife | 54.99

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

name | price
Wooden Puzzle | 12.99
Cotton T-Shirt | 14.99
Wool Beanie | 17.5

SELECT name, created_at FROM customers ORDER BY created_at DESC LIMIT 5;

name | created_at
Grace Nolan | 2024-06-19
Fatima Zahra | 2024-05-02
Erik Solberg | 2024-04-11
Dana Feld | 2024-03-22
Cole Tremblay | 2024-03-07

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

name | stock
Cotton T-Shirt | 300
Ceramic Mug Set | 200
Wireless Mouse | 120

Why should a “top 3 most expensive products” query include an ORDER BY?

ORDER BY makes the query run faster
Without it, LIMIT returns three arbitrary rows, so “top 3” has no defined meaning
LIMIT only works when ORDER BY is present

LIMIT runs fine on its own, but the rows it keeps are undefined. ORDER BY defines which rows are the “top” ones, so LIMIT keeps the ones you actually mean.

Paging With LIMIT and OFFSET

LIMIT n OFFSET m skips the first m rows, then returns the next n. This is how you build pages. With four rows per page, page one skips nothing, page two skips four, page three skips eight. As long as the ORDER BY stays the same, the pages line up with no gaps or overlaps. Every page-2 archive on a WordPress site is one of these queries under the hood.

SELECT name, price FROM products ORDER BY price DESC LIMIT 4 OFFSET 0;

name | price
27-inch Monitor | 219
Mechanical Keyboard | 89
Chef Knife | 54.99
Building Blocks Set | 44

SELECT name, price FROM products ORDER BY price DESC LIMIT 4 OFFSET 4;

name | price
Clean Database Design | 41
Cast Iron Pan | 39.99
USB-C Hub | 39.5
The DevAnswers Handbook | 34.5

SELECT name, price FROM products ORDER BY price DESC LIMIT 4 OFFSET 8;

name | price
SQL in 30 Days | 29.95
Wireless Mouse | 24.99
Ceramic Mug Set | 19.99
Wool Beanie | 17.5

OFFSET needs a stable sort. Paging only makes sense when the rows are in a fixed order. Pair OFFSET with the same ORDER BY on every page, or rows can shift between pages and you will see duplicates or skips.

Removing Duplicates With DISTINCT

DISTINCT goes right after SELECT and collapses duplicate rows into one. The shop has twelve orders, but how many different statuses are actually in play? Ask for the unique list:

SELECT DISTINCT status
FROM orders
ORDER BY status;

status
cancelled
delivered
pending
shipped

Twelve orders collapse to four statuses. The same trick answers “which countries do our customers come from?”: three customers are in Ireland, yet SELECT DISTINCT country FROM customers lists Ireland once. That is DISTINCT doing its job. But here is the part that trips people up: DISTINCT looks at the whole selected row, not just the first column. Add a second column and duplicates are decided by the full combination.

DISTINCT country

One row per unique country. Ireland collapses to a single row no matter how many customers live there.

DISTINCT country, city

One row per unique country and city pair. Ireland now appears three times, once per city (Dublin, Cork, Galway).

How many rows does this return? (There are 8 customers, in 6 different countries.)

SELECT DISTINCT country, city
FROM customers;

DISTINCT compares the whole country + city pair, and here all eight pairs are already unique: Ireland pairs with Dublin, Cork and Galway (three distinct rows), and every other customer has their own country and city. Cole’s city is NULL, which counts as its own value, so that pair is unique too. Nothing collapses, so you get 8 rows. Selecting DISTINCT country alone would give only 6.

See both side by side. Watch how the row count changes just from adding a column:

SELECT DISTINCT country FROM customers ORDER BY country;

country
Canada
Germany
Ireland
Morocco
Norway
UK

SELECT DISTINCT country, city FROM customers ORDER BY country, city;

country | city
Canada | NULL
Germany | Berlin
Ireland | Cork
Ireland | Dublin
Ireland | Galway
Morocco | Rabat
Norway | Oslo
UK | London

SELECT DISTINCT status FROM orders ORDER BY status;

status
cancelled
delivered
pending
shipped

SQLite vs other databases. SQLite, MySQL and PostgreSQL all use LIMIT and OFFSET. SQL Server does not: it uses SELECT TOP n or OFFSET ... FETCH. The SQL standard form is FETCH FIRST n ROWS ONLY. The queries here are the SQLite spelling, which also works on MySQL and Postgres.

Your Turn

Write a query that lists the distinct countries your customers come from, sorted alphabetically. Then hit Check my answer.

SELECT DISTINCT country
FROM customers
ORDER BY country;

What You Learned

LIMIT n returns at most n rows; LIMIT n OFFSET m skips m rows first, which is how you page results.
LIMIT without ORDER BY is nondeterministic, so a real top-N is always ORDER BY ... LIMIT n.
DISTINCT removes duplicate rows and applies to the whole selected row, so DISTINCT country, city gives unique pairs, not unique countries.
NULL still participates as a value in DISTINCT (all NULLs collapse together); the Canada + NULL pair is unique here because Canada appears only once.

You can now:

Limit and page results with LIMIT and OFFSET.
Remove duplicates with DISTINCT, and pair ORDER BY with LIMIT for a meaningful top-N.