What is LIMIT in SQL? Capping the number of rows for beginners

LIMIT is the SQL clause that says "give me at most N rows". Simple, and incredibly useful.

Picture a table with 50,000 videos. When you open YouTube, you don't see all 50,000 — the homepage shows you 12-20 recommendations. That's LIMIT. Same goes for e-commerce pagination, game leaderboards, social-feed pages, admin previews.

Why LIMIT matters

Three classic scenarios:

1. Top-N: top-10 sales of the week, top-3 posts by likes.
2. Pagination: "page 2 of 100", 20 products per page.
3. Preview / sanity check: while debugging, you want a peek at the first 5 rows to see what the data looks like.

Without LIMIT, the query returns everything. On big tables that means:

• Slow — millions of rows over the network.
• Pointless — the user won't see that many anyway.
• Risky — your app may run out of memory.

Basic syntax

SELECT *
FROM table_name
LIMIT N;

Example: a streaming service videos table.

SELECT title, views
FROM videos
LIMIT 5;

Returns the first 5 rows. Which 5 — without ORDER BY, unpredictable. The database picks them "as they sit".

LIMIT almost always comes with ORDER BY

If you want "top-5 by views", LIMIT without sorting gives you 5 random rows. You have to spell out the order:

SELECT title, views
FROM videos
ORDER BY views DESC
LIMIT 5;

Now it's "the 5 most-viewed videos". The database sorts first, then truncates.

Rule of thumb: LIMIT without ORDER BY only makes sense in three cases — preview during dev, picking a random row (ORDER BY RANDOM()), debugging. Everywhere else — write ORDER BY.

Example: streaming service

id	title	author	views	likes
1	Learn SQL in 30 minutes	Code Academy	250000	12000
2	Cats compile code	Funny Devs	1200000	95000
3	JOINs explained simply	DB Guru	80000	4500
4	A coder's borscht recipe	Tech Cooks	30000	1800
5	10 mistakes in SELECT	DB Guru	150000	8900
6	Top React hooks 2024	Web Dev	500000	25000
7	A short history of Linux	OS Lessons	90000	5500

Query: top 3 by views.

SELECT title, views
FROM videos
ORDER BY views DESC
LIMIT 3;

Result:

title	views
Cats compile code	1200000
Top React hooks 2024	500000
Learn SQL in 30 min	250000

Seven rows in, three rows out. The ones with the highest views.

OFFSET — skip the first N rows

The partner clause to LIMIT is OFFSET. It skips N rows before starting to return.

SELECT title, views
FROM videos
ORDER BY views DESC
LIMIT 3 OFFSET 3;

Reads as: "sort by views descending, skip 3 rows, return the next 3".

Result for our table (positions 4-6 by views):

title	views
Learn SQL in 30 min	250000
10 mistakes in SELECT	150000
A history of Linux	90000

That's pagination. Page 1 — LIMIT 3 OFFSET 0. Page 2 — LIMIT 3 OFFSET 3. Page 3 — LIMIT 3 OFFSET 6.

The pagination formula:

LIMIT page_size OFFSET (page_number - 1) * page_size

PostgreSQL vs MySQL — almost identical

Both understand LIMIT N and LIMIT N OFFSET M. Quick differences:

• MySQL also supports a shorthand LIMIT M, N — skip M, return N. Considered legacy; use LIMIT N OFFSET M instead.
• PostgreSQL also speaks the SQL-standard form: FETCH FIRST 10 ROWS ONLY (instead of LIMIT 10) and OFFSET 5 ROWS FETCH NEXT 10 ROWS ONLY. Verbose, but part of SQL:2008. In practice everyone just writes LIMIT/OFFSET.

For beginners: remember LIMIT N OFFSET M — works in both.

Bigger example: e-commerce pagination

products table:

id	name	category	price	popularity
1	iPhone 15	Electronics	999	980
2	Bosch kettle	Appliances	49	320
3	Book "Clean Code"	Books	22	850
4	DeLonghi coffee maker	Appliances	380	410
5	MacBook Air	Electronics	1450	920
6	Book "SQL in a Month"	Books	17	670
7	Pixel 8	Electronics	830	740
8	LG fridge	Appliances	720	280
9	Slow cooker	Appliances	89	560
10	Book "JavaScript"	Books	20	510

Goal: show page 2 (4 products per page), sorted by popularity.

SELECT id, name, popularity
FROM products
ORDER BY popularity DESC
LIMIT 4 OFFSET 4;

Result:

id	name	popularity
7	Pixel 8	740
6	Book "SQL in a Month"	670
9	Slow cooker	560
10	Book "JavaScript"	510

Page 1 (most popular): iPhone 15, MacBook Air, Book "Clean Code", Pixel 8. Page 2: rows 5-8 by popularity. Page 3 would be OFFSET 8.

Random sampling

Combo for a random row (handy for daily challenges, feature-of-the-day, etc.):

-- PostgreSQL
SELECT * FROM products ORDER BY RANDOM() LIMIT 1;

-- MySQL
SELECT * FROM products ORDER BY RAND() LIMIT 1;

Heads-up: on big tables RANDOM() / RAND() is heavy — the database has to generate a random number per row and sort. Fine for small tables, painful for millions of rows. For those, use other tricks (e.g. WHERE id = ROUND(RANDOM() * (SELECT MAX(id) FROM products))).

Common beginner mistakes

1. LIMIT without ORDER BY, expecting an order. People assume "the database returns in insertion order". It doesn't. Need an order? Write ORDER BY.

2. Pagination without ORDER BY. The sneakiest bug. Without sorting, page 2 may contain rows from page 1, and the user sees duplicates. Always sort by a unique column (typically id).

3. Huge OFFSETs. OFFSET 1000000 forces the database to generate a million rows and then throw them away. Slow. For big tables prefer keyset pagination: WHERE id > last_seen_id LIMIT N.

4. LIMIT with UNION. If you want LIMIT to apply to the whole UNION, parenthesise or put ORDER BY/LIMIT at the very end:

(SELECT name FROM authors UNION SELECT name FROM editors)
ORDER BY name
LIMIT 10;

5. Negative LIMIT or LIMIT 0. LIMIT 0 is valid — returns 0 rows (occasionally useful for sanity-checking a query without fetching data). Negative numbers throw an error.

6. LIMIT in UPDATE/DELETE — DB-specific. MySQL allows UPDATE … LIMIT 100. PostgreSQL doesn't — you have to use a subquery or CTE.

Quick recap

• LIMIT N — return at most N rows.
• OFFSET M — skip M rows before starting.
• Almost always paired with ORDER BY — otherwise the order is unpredictable.
• Used for top-N, pagination, previews.
• Same syntax in PostgreSQL and MySQL.
• Huge OFFSETs are slow — prefer keyset pagination on big tables.

Try it yourself

On the videos table:

1. Get the top 5 videos by likes.
2. Get page 2 (3 videos per page), sorted by views descending.
3. Pick a random video for "video of the day".

Write the queries on your own. Once you're comfortable with the ORDER BY + LIMIT combo, half of all real-world read queries reduce to this pattern.