🌱 BasicsbeginnerCommonly used

How to limit the number of rows returned in SQL

Restrict how many rows a query returns — useful for pagination and top-N queries.

Return first N rows (PostgreSQL / MySQL / SQLite)All databases

SELECT name, age
FROM users
ORDER BY age DESC
LIMIT 10;

Returns the first 10 rows. Always use ORDER BY with LIMIT for consistent results.

Return top N rows (SQL Server)SQL Server

SELECT TOP 10 name, age
FROM users
ORDER BY age DESC;

SQL Server uses TOP instead of LIMIT.

Pagination with LIMIT and OFFSETAll databases

-- Page 1: rows 1-10
SELECT name, email
FROM users
ORDER BY id
LIMIT 10 OFFSET 0;

-- Page 2: rows 11-20
SELECT name, email
FROM users
ORDER BY id
LIMIT 10 OFFSET 10;

OFFSET skips N rows before returning results. Used for pagination.

When to use: Building paginated APIs or UIs.

OFFSET pagination is slow on large tables. Use cursor-based pagination (WHERE id > last_seen_id) for better performance.

FETCH FIRST (SQL standard)PostgreSQL

SELECT name, age
FROM users
ORDER BY age DESC
FETCH FIRST 10 ROWS ONLY;

The SQL standard syntax. Supported by PostgreSQL, Oracle, DB2.

Common Mistakes

→Using LIMIT without ORDER BY — results are non-deterministic
→OFFSET pagination on large tables is slow — use keyset/cursor pagination for large datasets

Common Mistakes

Related Topics