What you will be able to do
After this lesson you can answer "who are the top three?", show a table a page at a time, and translate the same idea to SQL Server and to standard SQL. Limiting rows is what makes a result usable in an app, a report or a quick check of a huge table.
LIMIT: the first n rows
LIMIT goes at the very end of a query and says how many rows to return at most. On its own it returns an arbitrary set of rows, which is fine for peeking at a table and useless for anything else. For meaningful answers, combine it with ORDER BY. The three best-paid employees:
SELECT first_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;+------------+--------+
| first_name | salary |
+------------+--------+
| Meera | 180000 |
| Vikram | 150000 |
| Arjun | 125000 |
+------------+--------+Arjun and Kavya both earn 125000, so which of them takes third place is not guaranteed here. Adding a tie-breaker, as in the next examples, fixes that.
The lowest paid person is the mirror image, ORDER BY salary (ascending) with LIMIT 1:
SELECT first_name, salary
FROM employees
ORDER BY salary
LIMIT 1;+------------+--------+
| first_name | salary |
+------------+--------+
| Aman | 50000 |
+------------+--------+If the table has fewer rows than the limit, you simply get all of them. No error. And if two people tie for the last place within the limit, LIMIT cuts between them arbitrarily unless your ORDER BY has a tie-breaker.
OFFSET: skipping rows
OFFSET skips a number of rows before the limit begins counting. It comes after LIMIT:
SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC, last_name
LIMIT 5 OFFSET 5;+------------+-----------+--------+
| first_name | last_name | salary |
+------------+-----------+--------+
| Dev | Patel | 110000 |
| Ravi | Pillai | 95000 |
| Neha | Joshi | 90000 |
| Sameer | Khan | 90000 |
| Pooja | Reddy | 85000 |
+------------+-----------+--------+These are the people ranked sixth to tenth by pay. The first five were skipped. Like LIMIT, OFFSET means nothing without a defined order, which is why last_name is there as a tie-breaker: Neha Joshi and Sameer Khan both earn 90000.
Paging
Websites and reports show results in pages. If a page holds s rows and you want page number p, the query is LIMIT s OFFSET (p - 1) * s. With pages of five employees ordered by id:
SELECT id, first_name
FROM employees
ORDER BY id
LIMIT 5 OFFSET 10;+----+------------+
| id | first_name |
+----+------------+
| 11 | Anika |
| 12 | Yash |
| 13 | Lata |
| 14 | Omar |
| 15 | Pooja |
+----+------------+That is page 3, because pages 1 and 2 hold ids 1 to 10. The ORDER BY must put rows in a fixed, unique order. If rows could swap places between two requests, a person might see the same row on two pages, or miss one. Ordering by the primary key, or adding it as the last sort column, solves this.
You also usually want the total number of rows, so the page navigation can say "page 3 of 4". That is a separate query with COUNT(*), which a later lesson explains.
The same idea on other databases
LIMIT is not part of the SQL standard, though MySQL, PostgreSQL and SQLite all support it. Here is how the top three looks elsewhere. These are marked as not runnable here, because the output would be identical to the first example, and SQLite does not accept them:
SQL Server, simple top rows:
SELECT TOP 3 first_name, salary
FROM employees
ORDER BY salary DESC;The standard form, supported by PostgreSQL and by SQL Server (which needs ORDER BY and an OFFSET clause before FETCH):
SELECT first_name, salary
FROM employees
ORDER BY salary DESC
OFFSET 0 ROWS
FETCH FIRST 3 ROWS ONLY;On PostgreSQL the OFFSET 0 ROWS line can be left out. MySQL and SQLite do not understand FETCH FIRST, and the same SQL Server TOP syntax does not work on them either. A page on SQL Server uses OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY.
Why large offsets are slow
OFFSET 100000 does not jump to row 100,001. The database still has to produce, sort and throw away the first 100,000 rows. On small tables you will not notice. On big ones, deep pages get slower and slower.
A faster approach is keyset paging. Instead of "skip n rows", remember the last key you showed and ask for rows after it:
SELECT id, first_name
FROM employees
WHERE id > 15
ORDER BY id
LIMIT 3;+----+------------+
| id | first_name |
+----+------------+
| 16 | Karan |
| 17 | Sana |
| 18 | Ravi |
+----+------------+Here 15 was the last id on the previous page. With an index on id, the database goes straight to the right spot. The trade-off is that you can only move to the next or previous page, not jump to page 40, and the sort key must be unique (or combined with a unique column).
Differences between databases
- MySQL, PostgreSQL, SQLite:
LIMIT nandLIMIT n OFFSET m. MySQL also accepts the short formLIMIT m, n, where the offset comes first. It is easy to get backwards, so useOFFSET. - SQL Server:
TOP n, orOFFSET m ROWS FETCH NEXT n ROWS ONLYafterORDER BY. - Standard / PostgreSQL:
FETCH FIRST n ROWS ONLY, with optionalOFFSET m ROWS. - In SQLite and MySQL,
OFFSETcan only be written after aLIMIT.
The MySQL ORDER BY and LIMIT lesson covers the MySQL form too.
Common mistakes
LIMITwithoutORDER BY, and then treating the rows as "the top" ones.- No tie-breaker in a paged query, so rows repeat or vanish across pages.
- Calculating the offset wrongly. Page 1 is offset 0, not offset
s. - Using
LIMITon SQL Server, which gives a syntax error. UseTOPorFETCH. - Deep
OFFSETon huge tables when keyset paging would do.
Exercise
Show page 2 of the employees when each page has four rows and employees are ordered by hire_date (earliest first), with id as the tie-breaker. Show first_name and hire_date.
Show one possible answer
SELECT first_name, hire_date
FROM employees
ORDER BY hire_date, id
LIMIT 4 OFFSET 4;+------------+------------+
| first_name | hire_date |
+------------+------------+
| Neha | 2019-02-18 |
| Arjun | 2019-07-01 |
| Pooja | 2019-09-23 |
| Kavya | 2020-01-20 |
+------------+------------+Page 2 skips (2 - 1) * 4 = 4 rows.
Key points
- LIMIT n returns at most n rows. Pair it with ORDER BY, otherwise you get an arbitrary n rows.
- OFFSET m skips the first m rows, so page p of size s uses OFFSET (p - 1) * s.
- SQL Server writes TOP n, or OFFSET ... FETCH. The standard form is FETCH FIRST n ROWS ONLY.
- A paging query needs a deterministic order, which usually means adding a unique tie-breaker column.
- Large offsets get slower. Keyset paging, which filters with WHERE id > last_seen_id, avoids that.