"The 3 most expensive products." "The 10 newest orders." Sort the rows, then keep only the first ones.
LIMIT
sql
SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 3;LIMIT n keeps the first n rows of the result. It comes after ORDER BY.
products, ORDER BY price DESC
| name | price |
|---|---|
| Root Hoodie | 49.95 |
| Mystery Box | 39.00 |
| Pod Golem Plush | 34.50 |
| Kraken Plush | 29.99 |
| Daemon Cat Plush | 24.00 |
Always use ORDER BY with LIMIT. Without it, the rows have no fixed order, and "the first 3" can be any 3 rows.
OFFSET: pages
OFFSET n skips the first n rows. With LIMIT, it gives one page of a list:
| Page (5 rows each) | Clause |
|---|---|
| 1 | LIMIT 5 OFFSET 0 (or just LIMIT 5) |
| 2 | LIMIT 5 OFFSET 5 |
| 3 | LIMIT 5 OFFSET 10 |
The offset for page p is (p - 1) × rows per page.
Other spellings
| System | First 3 rows |
|---|---|
| PostgreSQL, MySQL, SQLite | LIMIT 3 |
| SQL standard (PostgreSQL, Oracle) | FETCH FIRST 3 ROWS ONLY |
| SQL Server | SELECT TOP 3 name FROM … |