LIMIT, OFFSET, and DISTINCT

Taking just the top of the list

Real tables have millions of rows. You almost never want all of them. LIMIT n cuts the result off after n rows, and it combines beautifully with ORDER BY:

SELECT title, rating FROM movies
ORDER BY rating DESC
LIMIT 3;

Sort best-first, keep three: that is a top-3 query. Add OFFSET m to skip the first m rows before counting, which is how apps build page 2 of results: LIMIT 10 OFFSET 10 shows results 11 through 20.

An important habit: LIMIT without ORDER BY gives you some n rows, not the top n. Always sort before you cut.

Taking the top three by rating

ORDER BY decides which rows count as "top", and LIMIT cuts the result off after that many rows.

CREATE TABLE movies (
  title TEXT,
  year INTEGER,
  rating REAL,
  genre TEXT
);

INSERT INTO movies VALUES ('Inside Out', 2015, 8.1, 'animation');
INSERT INTO movies VALUES ('The Matrix', 1999, 8.7, 'scifi');
INSERT INTO movies VALUES ('Arrival', 2016, 7.9, 'scifi');
INSERT INTO movies VALUES ('Paddington 2', 2017, 7.8, 'family');
INSERT INTO movies VALUES ('Alien', 1979, 8.5, 'scifi');

SELECT title, rating FROM movies
ORDER BY rating DESC
LIMIT 3;

Output

The Matrix|8.7
Alien|8.5
Inside Out|8.1

The clause order is not negotiable: LIMIT comes last, after ORDER BY. The database sorts all five rows and then hands over the first three, which means the two lowest-rated movies are computed and discarded.

This pairing is behind almost every "top N" list you have ever seen on a website, from best sellers to most recent posts.

Removing duplicates with DISTINCT

Ask for just the genre column and you get one value per row, duplicates included: scifi prints three times. SELECT DISTINCT collapses identical result rows into one:

SELECT DISTINCT genre FROM movies ORDER BY genre;

The next block runs the plain version first, then a divider, then the DISTINCT version so you can see the difference in one output.

Collapsing repeated values with DISTINCT

The first query returns one genre value per movie row, repeats included. The second adds DISTINCT so each genre appears once.

CREATE TABLE movies (
  title TEXT,
  year INTEGER,
  rating REAL,
  genre TEXT
);

INSERT INTO movies VALUES ('Inside Out', 2015, 8.1, 'animation');
INSERT INTO movies VALUES ('The Matrix', 1999, 8.7, 'scifi');
INSERT INTO movies VALUES ('Arrival', 2016, 7.9, 'scifi');
INSERT INTO movies VALUES ('Paddington 2', 2017, 7.8, 'family');
INSERT INTO movies VALUES ('Alien', 1979, 8.5, 'scifi');

SELECT genre FROM movies;
SELECT '---';
SELECT DISTINCT genre FROM movies ORDER BY genre;

Output

animation
scifi
scifi
family
scifi
---
animation
family
scifi

Five rows become three. DISTINCT works on the whole result row, not on one column, so SELECT DISTINCT genre, year would keep any genre that appears with two different years.

The ORDER BY genre on the second query is there for a practical reason. Without it the three unique values could come back in any order, and a list meant for a dropdown menu or a filter sidebar should be predictable.

LIMIT without ORDER BY is a trap

A leaderboard query that ends in LIMIT 10 with no ORDER BY returns ten rows, with no guarantee that they are the top ten.

LIMIT does not rank anything. It simply stops after ten rows in whatever order the database happened to produce them, which may reflect storage layout, caching, or the plan the optimizer chose today. The query runs without error and the page looks fine, which is exactly what makes the bug easy to ship.

Worse, the answer can change between runs on identical data, so it may pass every test you write and still be wrong in production. Sort first, then limit:

SELECT name, score FROM players
ORDER BY score DESC
LIMIT 10;

The two most recent movies

"Most recent" is a sorting instruction, and "two" is a limiting one, so the query needs both clauses.

CREATE TABLE movies (
  title TEXT,
  year INTEGER,
  rating REAL,
  genre TEXT
);

INSERT INTO movies VALUES ('Inside Out', 2015, 8.1, 'animation');
INSERT INTO movies VALUES ('The Matrix', 1999, 8.7, 'scifi');
INSERT INTO movies VALUES ('Arrival', 2016, 7.9, 'scifi');
INSERT INTO movies VALUES ('Paddington 2', 2017, 7.8, 'family');
INSERT INTO movies VALUES ('Alien', 1979, 8.5, 'scifi');

SELECT title, year FROM movies
ORDER BY year DESC
LIMIT 2;

Output

Paddington 2|2017
Arrival|2016

ORDER BY year DESC puts the newest films first, and LIMIT 2 keeps the first two of that ordering. Reversing the direction to plain ORDER BY year with the same limit would have returned the two oldest movies instead, so DESC is carrying the whole meaning of the word "recent" here.

Working out an OFFSET for pagination

For an app showing 10 search results per page, page 3 needs LIMIT 10 OFFSET 20.

OFFSET skips rows that have already been shown. Pages 1 and 2 together displayed the first 20 rows, so page 3 has to start after them.

The general formula is OFFSET (page - 1) × page_size.

Pagination only makes sense with an ORDER BY, for the reason described above. Without a stable sort, a row can appear on both page 1 and page 2 while another is never shown at all.