Learn SQL  /  Filtering & Sorting  /  LIMIT & OFFSET

LIMIT & OFFSET

LIMIT restricts how many rows a query returns, and OFFSET skips a number of rows first — together they are the standard way to paginate results.

Basic LIMIT

R Code
SELECT name, score
FROM students
ORDER BY score DESC
LIMIT 2;
Output
name  | score
Meera | 92
Rafi  | 88
💡 Tip
Always pair LIMIT with ORDER BY. Without a defined sort order, which rows a database happens to return first is not guaranteed - LIMIT on an unsorted query can return different rows each time it runs.

OFFSET — Skipping Rows

R Code
SELECT name, score
FROM students
ORDER BY score DESC
LIMIT 2 OFFSET 2;   -- skip the top 2, then take the next 2
Output
name  | score
Zayn  | 76
Nadia | 65

Pagination Pattern

R Code
-- page 1 (rows 1-10)
SELECT * FROM students ORDER BY id LIMIT 10 OFFSET 0;

-- page 2 (rows 11-20)
SELECT * FROM students ORDER BY id LIMIT 10 OFFSET 10;

Advanced: Finding the Top-N Per Group

🚀 Advanced Example
LIMIT alone only gives an overall top-N. Getting the top-N WITHIN each group needs a window function - see the Window Functions page for the full technique.
R Code
-- highest scorer overall
SELECT name, score
FROM students
ORDER BY score DESC
LIMIT 1;
Output
name  | score
Meera | 92

Key Points

  • LIMIT restricts the number of rows returned; OFFSET skips a number of rows first.
  • Always combine LIMIT with ORDER BY - an unsorted LIMIT is not guaranteed to be consistent.
  • LIMIT + OFFSET together implement standard page-by-page pagination.
  • For a top-N result within each group, use a window function instead of plain LIMIT.