How to limit rows and paginate in SQL

1ORDER BY … LIMIT n: the top N rowsRecommended

Put LIMIT n at the very end of the query, after ORDER BY. The database sorts the whole result first and then keeps the first n rows, so ORDER BY score DESC gives the highest scores and plain ORDER BY score the lowest. The same clause works in SQLite, PostgreSQL and MySQL.

CREATE TABLE players (id INTEGER PRIMARY KEY, name TEXT, score INTEGER);
INSERT INTO players (name, score) VALUES
    ('ana', 92), ('ben', 85), ('cara', 64),
    ('dev', 78), ('eli', 97), ('fay', 70);

-- Top 3 scores: sort first, then keep the first 3 rows
SELECT name, score
FROM players
ORDER BY score DESC
LIMIT 3;

-- Bottom 2: the same LIMIT with the opposite sort
SELECT name, score
FROM players
ORDER BY score
LIMIT 2;

The first query returns eli (97), ana (92) and ben (85); the second returns cara (64) and fay (70). Leave out the ORDER BY and LIMIT 3 still returns three rows, but which three is up to the database: SQL tables have no built-in order, and the rows you get can change after an update, a new index or a different query plan. If you need the result in a set order, say so with ORDER BY.

2LIMIT n OFFSET m: page k of the results

OFFSET m skips the first m rows of the sorted result, and LIMIT then takes the next n. For page k with size rows per page, the offset is (k - 1) * size: page 1 skips nothing, page 2 skips one page, and so on. The last page holds whatever is left. To show a page count, divide the row count by the page size, rounding up.

CREATE TABLE players (id INTEGER PRIMARY KEY, name TEXT, score INTEGER);
INSERT INTO players (name, score) VALUES
    ('ana', 92), ('ben', 85), ('cara', 64), ('dev', 78),
    ('eli', 97), ('fay', 70), ('gus', 81);

-- Page 1 of 3 rows: OFFSET (1 - 1) * 3 = 0
SELECT name, score FROM players
ORDER BY score DESC
LIMIT 3 OFFSET 0;

-- Page 2: OFFSET (2 - 1) * 3 = 3
SELECT name, score FROM players
ORDER BY score DESC
LIMIT 3 OFFSET 3;

-- Page 3 holds what is left
SELECT name, score FROM players
ORDER BY score DESC
LIMIT 3 OFFSET 6;

-- How many pages: round the row count up
SELECT count(*) AS total_rows,
       (count(*) + 3 - 1) / 3 AS pages
FROM players;

Seven players at three per page make pages of eli, ana, ben, gus, dev, fay and just cara, and the count query prints 7 rows and 3 pages. (count + size - 1) / size rounds up because both sides are integers, so the division drops the remainder. In application code, pass the limit and offset as query parameters (LIMIT ? OFFSET ?) rather than pasting numbers into the SQL string. SQLite and MySQL also accept the short form LIMIT 3, 3, with the offset first; the table in section 7 has the details.

3Select the nth row: LIMIT 1 OFFSET n - 1

The nth row of a sorted result is a page of one row: skip n - 1 rows and keep 1. This is also the usual answer to "find the second-highest salary". Wrapped in a scalar subquery, the query returns the value itself, and NULL when there is no such row; the bare query returns no row at all.

CREATE TABLE players (id INTEGER PRIMARY KEY, name TEXT, score INTEGER);
INSERT INTO players (name, score) VALUES
    ('ana', 92), ('ben', 85), ('cara', 64),
    ('dev', 78), ('eli', 97), ('fay', 70);

-- The 3rd-highest score: skip 2 rows, keep 1
SELECT name, score FROM players
ORDER BY score DESC
LIMIT 1 OFFSET 2;

-- The second-highest score as a single value
SELECT (SELECT score FROM players
        ORDER BY score DESC
        LIMIT 1 OFFSET 1) AS second_best;

-- Past the end: no row, so nothing prints
SELECT name FROM players
ORDER BY score DESC
LIMIT 1 OFFSET 10;

-- A subquery returns NULL instead
SELECT (SELECT name FROM players
        ORDER BY score DESC
        LIMIT 1 OFFSET 10) AS eleventh;

The third-highest score belongs to ben (85) and the second-highest value is 92. The query with OFFSET 10 prints nothing, because six rows run out before the offset does, while the subquery version prints NULL. Watch out for ties: if two players shared the top score, OFFSET 1 would return that same score again. For the second-highest distinct value, sort SELECT DISTINCT score instead, or rank with DENSE_RANK() as in section 6.

4Keep pages stable with a tie-breaker column

When several rows share the sort value, ORDER BY score doesn't say which of them comes first, and the database is free to pick a different order each time. With paging that is a real bug: page 1 and page 2 are separate queries, so a tied row can appear on both pages while another never appears. Add a unique column, usually the primary key, as the last sort key so that every row has exactly one position.

CREATE TABLE players (id INTEGER PRIMARY KEY, name TEXT, score INTEGER);
INSERT INTO players (name, score) VALUES
    ('ana', 92), ('ben', 85), ('cara', 85), ('dev', 78),
    ('eli', 92), ('fay', 70), ('gus', 85);

-- Three players share 85, so ORDER BY score alone leaves their order open.
-- Add a unique column last: every row now has exactly one position.
SELECT id, name, score FROM players
ORDER BY score DESC, id
LIMIT 3 OFFSET 0;

SELECT id, name, score FROM players
ORDER BY score DESC, id
LIMIT 3 OFFSET 3;

The three players on 85 straddle the page break: ben (id 2) ends page 1, and cara (3) and gus (7) start page 2. Because id settles every tie, rerunning either query always gives the same split. Without it, a database could return gus on page 1 one time and ben the next. The tie-breaker can be any column, or set of columns, that is unique; it only decides the order inside a group of equal scores.

5Keyset pagination: seek past the last row instead of OFFSET

OFFSET gets slower the deeper you page: for OFFSET 100000 the database still finds, sorts and throws away 100,000 rows before it returns the page. Keyset pagination (also called the seek method) remembers the sort key of the last row the user saw and asks for the rows after it with a WHERE clause. With an index on the sort columns, the database jumps straight to that point, so page 5,000 costs about the same as page 1. A row value comparison, (score, id) < (85, 7), says "after that row" in one line; SQLite has supported row values since 3.15.

CREATE TABLE players (id INTEGER PRIMARY KEY, name TEXT, score INTEGER);
INSERT INTO players (name, score) VALUES
    ('ana', 92), ('ben', 85), ('cara', 85), ('dev', 78),
    ('eli', 92), ('fay', 70), ('gus', 85);
CREATE INDEX players_score_id ON players (score, id);

-- Page 1: no WHERE yet
SELECT id, name, score FROM players
ORDER BY score DESC, id DESC
LIMIT 3;

-- Page 2: start after the last row of page 1 (score 85, id 7)
SELECT id, name, score FROM players
WHERE (score, id) < (85, 7)
ORDER BY score DESC, id DESC
LIMIT 3;

-- The same filter without row values (works everywhere)
SELECT id, name, score FROM players
WHERE score < 85 OR (score = 85 AND id < 7)
ORDER BY score DESC, id DESC
LIMIT 3;

Page 1 ends with gus (score 85, id 7), so page 2 asks for rows before (85, 7) in that order and gets cara, ben and dev; the spelled-out OR form returns the same three rows. Both sort keys run in the same direction here (score DESC, id DESC) because a row value compares its columns left to right in one direction. The comparison must match the sort: < for descending, > for ascending. Keyset paging also copes better with new rows: an insert above the current page shifts every later OFFSET page by one row, but not a page that starts from a key. The cost is that you can only step to the next or previous page, not jump to page 37, so many apps use it for infinite scroll and "load more" buttons.

6Top N with ties: RANK() <= n

LIMIT 3 always stops at three rows, even when the fourth row has the same score as the third. To keep everyone who ties, number the rows with a window function and filter on the number. RANK() gives tied rows the same rank and leaves gaps after them; DENSE_RANK() leaves no gaps, so <= 3 means "the three highest distinct scores". To do this for each group, such as the top 3 per team, add PARTITION BY; see select the first row in each group.

CREATE TABLE players (id INTEGER PRIMARY KEY, name TEXT, score INTEGER);
INSERT INTO players (name, score) VALUES
    ('ana', 92), ('ben', 85), ('cara', 85), ('dev', 78),
    ('eli', 92), ('fay', 70), ('gus', 85);

-- Top 3 with ties: everyone whose rank is 3 or better
SELECT name, score, rnk
FROM (
    SELECT name, score,
           RANK() OVER (ORDER BY score DESC) AS rnk
    FROM players
)
WHERE rnk <= 3
ORDER BY rnk, name;

-- The top 3 distinct scores, with everyone who has them
SELECT name, score, drnk
FROM (
    SELECT name, score,
           DENSE_RANK() OVER (ORDER BY score DESC) AS drnk
    FROM players
)
WHERE drnk <= 3
ORDER BY drnk, name;

RANK() numbers the two 92s as 1 and the three 85s as 3 (rank 2 is skipped), so "top 3 with ties" returns five players. DENSE_RANK() numbers the 85s as 2, which lets dev (78) in at 3: six players across three distinct scores. The filter has to sit in an outer query because a WHERE clause can't refer to a window function in the same SELECT. SQLite has had window functions since 3.25. PostgreSQL and SQL Server also have a shortcut for the RANK() case, shown in the next section.

7Other databases: TOP, OFFSET … FETCH and LIMIT m, n

LIMIT is not in the SQL standard, although SQLite, PostgreSQL and MySQL all accept it. The standard spelling is OFFSET m ROWS FETCH NEXT n ROWS ONLY, which PostgreSQL and SQL Server (2012 and later) support; older SQL Server code uses SELECT TOP n. Every form below was run on PostgreSQL 18, MySQL 8.4 and SQL Server's T-SQL (through Babelfish) except where the note says otherwise. Oracle has no LIMIT: Oracle 12c and later accept FETCH FIRST n ROWS ONLY and OFFSET m ROWS FETCH NEXT n ROWS ONLY, while older versions sort in a subquery and filter on ROWNUM in the outer query.