LIMIT & OFFSET SQL practice problemMediumVerified answer

Standard SQL Pagination: OFFSET … FETCH NEXT …

The ANSI-standard pagination form is `OFFSET M ROWS FETCH NEXT N ROWS ONLY` (shown as a comment in the editor). Return page 2 of employees with page size 4, ordered by employee_id. The canonical solution uses the universal LIMIT/OFFSET form for cross-engine grading. Show employee_id, first_name, last_name.

  • CTEs
  • Sorting
  • Top-N

Interview brief

Understand the request

The ANSI-standard pagination form is `OFFSET M ROWS FETCH NEXT N ROWS ONLY` (shown as a comment in the editor). Return page 2 of employees with page size 4, ordered by employee_id. The canonical solution uses the universal LIMIT/OFFSET form for cross-engine grading. Show employee_id, first_name, last_name.

Data you will use

Review the relevant tables before deciding how to join, filter, or aggregate them.

employees

  • employee_idINTEGER
  • first_nameTEXT
  • last_nameTEXT

Hints, when you need them

Open one clue at a time so you still do the reasoning.

Hint 1

Page 2 with page_size 4 → skip 4, take 4. ANSI form: `OFFSET 4 ROWS FETCH NEXT 4 ROWS ONLY`. Universal form: `LIMIT 4 OFFSET 4`.

Hint 2

ANSI is verbose but portable across PostgreSQL/Oracle/DB2/SQL Server. MySQL and many SQLite builds (including this lab) require LIMIT/OFFSET.

Hint 3

ORDER BY employee_id LIMIT 4 OFFSET 4 -- or OFFSET 4 ROWS FETCH NEXT 4 ROWS ONLY on PG/SQL Server

Verified SQL answer

Attempt the problem first, then compare structure and reasoning—not just syntax.

Reveal solution and explanation
SELECT employee_id, first_name, last_name FROM employees ORDER BY employee_id LIMIT 4 OFFSET 4;

Why this works

Standard pagination is the most portable form across enterprise databases. Use OFFSET … FETCH NEXT on PostgreSQL/SQL Server/Oracle, LIMIT/OFFSET on MySQL/SQLite. The canonical solution here is pinned to LIMIT/OFFSET for consistent grading.

Expected result

Use this output to verify values, aliases, ordering, and row count.

employee_idfirst_namelast_name
5DavidBrown
6LisaDavis
7TomWilson
8AmyTaylor

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics: