0Pricing
SQL Interview Prep · Lesson

LIMIT, OFFSET and FETCH FIRST

Pagination syntax across MySQL, Postgres, and SQL Server.

LIMIT, OFFSET and FETCH FIRST is a free SQL Interview Prep lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Pagination Is a Cross-Dialect Question

"How would you fetch the first 10 rows?" sounds trivial until the interviewer adds "and do it in standard SQL" or "now in SQL Server." The syntax for limiting rows is one of the most fragmented features across databases.

This lesson maps the three main forms: LIMIT (MySQL/Postgres/SQLite), OFFSET ... FETCH (ANSI standard, SQL Server), and the older TOP (SQL Server).

LIMIT in MySQL and PostgreSQL

The most common form is LIMIT n, which caps the result at n rows. It is supported by MySQL, PostgreSQL, and SQLite.

Crucially, LIMIT is applied after ORDER BY in logical execution order, so you almost always pair them. Limiting without sorting gives you an arbitrary set of rows.

SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 10;

OFFSET for Skipping Rows

Pagination needs an offset to skip earlier pages. OFFSET m skips the first m rows before the limit applies.

To get page 3 with a page size of 10, you skip 20 rows and take 10. The general formula is OFFSET = (page - 1) * page_size.

SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 10 OFFSET 20;

MySQL's Comma Syntax

MySQL also accepts a two-argument comma form: LIMIT offset, count. Note the order is reversed from the keyword form, which trips people up.

LIMIT 20, 10 means skip 20, then take 10, identical to LIMIT 10 OFFSET 20. Prefer the keyword form for clarity in interviews.

SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 20, 10;

The ANSI Standard: FETCH FIRST

The SQL standard uses OFFSET ... ROWS FETCH FIRST ... ROWS ONLY. PostgreSQL, Oracle 12c+, DB2, and SQL Server 2012+ all support it.

It reads verbosely but is the portable choice. ROW and ROWS are interchangeable; FIRST and NEXT are synonyms.

SELECT name, salary
FROM employees
ORDER BY salary DESC
OFFSET 20 ROWS
FETCH FIRST 10 ROWS ONLY;

SQL Server TOP

Older SQL Server code uses TOP n right after SELECT. It has no built-in offset, so it cannot paginate beyond the first page on its own.

An interviewer may ask you to convert TOP to the standard form. SELECT TOP 10 with ORDER BY equals FETCH FIRST 10 ROWS ONLY.

SELECT TOP 10 name, salary
FROM employees
ORDER BY salary DESC;

TOP WITH TIES

A nice SQL Server feature: TOP n WITH TIES returns extra rows when they tie with the last row on the ORDER BY key.

If three people share the 10th-highest salary, TOP 10 WITH TIES returns all of them, possibly 12 rows. This is the equivalent of FETCH FIRST ... WITH TIES in the standard.

SELECT TOP 10 WITH TIES name, salary
FROM employees
ORDER BY salary DESC;

ORDER BY Is Mandatory for Deterministic Paging

The single biggest interview point here: OFFSET/LIMIT without a deterministic ORDER BY is unsafe. The engine may pick different rows for "page 2" than it did for "page 1" because the underlying order is undefined.

Always sort on a unique or unique-enough key set. If your sort column has duplicates, add the primary key as a tiebreaker so pages do not overlap or skip rows.

SELECT id, name, salary
FROM employees
ORDER BY salary DESC, id ASC
LIMIT 10 OFFSET 20;

Why OFFSET Pagination Scales Poorly

Senior interviewers probe performance. A large OFFSET still forces the database to generate and discard all the skipped rows. OFFSET 100000 LIMIT 10 reads 100,010 rows to return 10.

The cost grows linearly with the page number, making deep pagination slow on big tables.

Keyset (Seek) Pagination

The scalable alternative is keyset pagination: instead of skipping by count, you filter past the last value you saw. This uses an index range scan and stays fast at any depth.

You remember the last row's sort key from the previous page and ask for rows beyond it. Mentioning keyset pagination is a strong senior signal.

SELECT id, name, created_at
FROM events
WHERE created_at < '2026-01-01 10:00:00'
ORDER BY created_at DESC
LIMIT 10;

Cheat Sheet by Dialect

Memorize this table for fast interview recall:

  • MySQL / Postgres / SQLite: LIMIT n OFFSET m
  • Standard / SQL Server 2012+ / Oracle 12c+: OFFSET m ROWS FETCH FIRST n ROWS ONLY
  • Old SQL Server: SELECT TOP n (no offset)
  • Old Oracle (pre-12c): filter on ROWNUM in a subquery

Quick Check

Pick the correct page-3 query for a page size of 10.

Recap

Pagination essentials:

  • LIMIT/OFFSET for MySQL, Postgres, SQLite; OFFSET ... FETCH FIRST for the standard and modern SQL Server; TOP for legacy SQL Server.
  • OFFSET = (page - 1) * page_size.
  • Always pair with a deterministic ORDER BY, adding a unique tiebreaker.
  • Large offsets are slow; prefer keyset pagination for deep paging.

Frequently asked questions

Is the “LIMIT, OFFSET and FETCH FIRST” lesson free?

Yes — the full text of “LIMIT, OFFSET and FETCH FIRST” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “LIMIT, OFFSET and FETCH FIRST”?

Pagination syntax across MySQL, Postgres, and SQL Server. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Interview Prep?

No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “LIMIT, OFFSET and FETCH FIRST” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Interview Prep lesson?

Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. Multi-Column Sorting and NULL Placement
  2. LIMIT, OFFSET and FETCH FIRST
  3. Returning the Top-N Rows Reliably
  4. Sorting by Expressions and Aliases
← Back to SQL Interview Prep