SQL LIMIT, TOP and FETCH: Return the First N Rows

Without LIMIT, a query returns every matching row. With it, you get at most the number you ask for:

SELECT id, first_name, last_name, city
FROM users
ORDER BY id
LIMIT 5;

▶ Run this query in the SQL Playground

SELECT columns
FROM table
[WHERE ...]
[ORDER BY ...]
LIMIT n [OFFSET m];

LIMIT is applied last, after WHERE, GROUP BY, HAVING and ORDER BY. The database builds and sorts the result, then hands back the first n rows. If fewer rows match, you simply get fewer.

LIMIT on its own takes whichever rows the database finds first, and that can change from run to run. For a meaningful "top N", sort first. The three highest salaries:

SELECT first_name, last_name, department, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;

▶ Run this query in the SQL Playground

Top N also works on grouped results. The five customers who spent the most:

SELECT user_id,
       COUNT(*) AS orders,
       ROUND(SUM(total), 2) AS spent
FROM orders
GROUP BY user_id
ORDER BY spent DESC
LIMIT 5;

▶ Run this query in the SQL Playground

OFFSET m skips the first m rows before LIMIT counts. LIMIT 3 OFFSET 2 returns rows 3, 4 and 5:

SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3 OFFSET 2;

▶ Run this query in the SQL Playground

A common use is the Nth highest value. The second-highest distinct order total:

SELECT DISTINCT total
FROM orders
ORDER BY total DESC
LIMIT 1 OFFSET 1;

▶ Run this query in the SQL Playground

DISTINCT matters: without it, two orders with the same top total would make the "second" value equal to the first.

To show results in pages of a fixed size, use LIMIT page_size OFFSET (page_number - 1) * page_size. With 5 products per page, page 3 is:

SELECT id, name, price
FROM products
ORDER BY id
LIMIT 5 OFFSET 10;

▶ Run this query in the SQL Playground

Keyset pagination for large tables

OFFSET has to read and throw away every skipped row, so page 10,000 is slow. Keyset (or "seek") pagination remembers the last row of the previous page and continues from there, which an index can serve directly:

SELECT id, name, price
FROM products
WHERE id > 10      -- the last id shown on the previous page
ORDER BY id
LIMIT 5;

▶ Run this query in the SQL Playground

It returns the same rows as the OFFSET version, but the cost no longer grows with the page number. The trade-off: you can only move to the next or previous page, not jump straight to page 400.

The idea is the same everywhere; the syntax is not:

-- SQL Server
SELECT TOP 5 first_name, salary
FROM employees
ORDER BY salary DESC;

-- SQL Server: include rows tied with the 5th
SELECT TOP 5 WITH TIES first_name, salary
FROM employees
ORDER BY salary DESC;

-- Standard SQL
SELECT first_name, salary
FROM employees
ORDER BY salary DESC
FETCH FIRST 5 ROWS ONLY;

SQLite (the Playground) supports LIMIT and OFFSET but not TOP or FETCH FIRST.

Five errors and how to avoid them.

SELECT * FROM orders
LIMIT 5;  -- "the latest orders"?

SELECT * FROM orders
ORDER BY order_date DESC, id DESC
LIMIT 5;

SELECT * FROM employees
ORDER BY salary
LIMIT 3;

SELECT * FROM employees
ORDER BY salary DESC
LIMIT 3;

-- page 2, 20 per page
LIMIT 20 OFFSET 40

LIMIT 20 OFFSET 20

SELECT TOP 5 * FROM products
ORDER BY price DESC;

SELECT * FROM products
ORDER BY price DESC
LIMIT 5;

SELECT total FROM orders
ORDER BY total DESC
LIMIT 1 OFFSET 1;

SELECT DISTINCT total FROM orders
ORDER BY total DESC
LIMIT 1 OFFSET 1;

5 exercises on the real Playground tables.

Write each query yourself first. Every solution has been run against the Playground database.

1. 1Easy Show the 5 cheapest products. Table: products

   Show solution

   SQLite · runs in the Playground

   SELECT name, price
   FROM products
   ORDER BY price
   LIMIT 5;

   ▶ Run this query in the SQL Playground

   Ascending order puts the lowest prices first.

2. 2Easy Show the 3 most recent orders. Table: orders

   Show solution

   SQLite · runs in the Playground

   SELECT id, order_date, total
   FROM orders
   ORDER BY order_date DESC, id DESC
   LIMIT 3;

   ▶ Run this query in the SQL Playground

   id breaks ties between orders on the same date.

3. 3Medium Show page 2 of the employee list sorted by last name, with 10 employees per page. Table: employees

   Show solution

   SQLite · runs in the Playground

   SELECT last_name, first_name, department
   FROM employees
   ORDER BY last_name, first_name, id
   LIMIT 10 OFFSET 10;

   ▶ Run this query in the SQL Playground

   Page 2 skips the first page of 10.

4. 4Medium Find the third-highest distinct salary. Table: employees

   Show solution

   SQLite · runs in the Playground

   SELECT DISTINCT salary
   FROM employees
   ORDER BY salary DESC
   LIMIT 1 OFFSET 2;

   ▶ Run this query in the SQL Playground

   OFFSET 2 skips the two highest distinct salaries.

5. 5Medium Show the 3 product categories with the most products. Table: products

   Show solution

   SQLite · runs in the Playground

   SELECT category, COUNT(*) AS products
   FROM products
   GROUP BY category
   ORDER BY products DESC, category
   LIMIT 3;

   ▶ Run this query in the SQL Playground

   LIMIT applies after grouping and sorting.