Topic practice collection

LIMIT & OFFSET SQL practice questions

Solve pagination, deterministic ordering, and top-N query problems.

Exercises
22
Difficulty
Beginner to Intermediate

What this collection tests

Skills and query patterns

Pagination, LIMIT, OFFSET, top-N results, and ordering

Runnable exercises

Choose a problem

Read the brief, solve it in the live editor, then reveal the verified SQL and explanation.

  1. Problem 1Easy

    Basic LIMIT (with ORDER BY)

    Return the first 5 employees from the table.

    • CTEs
    • Sorting
    • Top-N
    Open problem →
  2. Problem 2Easy

    Top N with ORDER BY DESC + LIMIT

    Return the top 3 highest-paid employees.

    • CTEs
    • Sorting
    • Top-N
    Open problem →
  3. Problem 3Easy

    Preview the First N Rows

    Return any 4 employees as a data sample.

    • Sorting
    • Top-N
    Open problem →
  4. Problem 4Easy

    Skip Rows with OFFSET

    Return employees 4 through 6 (skip the first 3).

    • CTEs
    • Sorting
    • Top-N
    Open problem →
  5. Problem 5Easy

    Pagination: Page 1

    Return the first page of results (5 employees, starting from the beginning).

    • CTEs
    • Sorting
    • Top-N
    Open problem →
  6. Problem 6Easy

    Pagination: Page 3

    Return page 3 of results (5 employees, starting after the first 10).

    • CTEs
    • Sorting
    • Top-N
    Open problem →
  7. Problem 7Medium

    Top N within a Filter

    Return the top 2 highest-paid employees in Engineering (department_id = 1).

    • CTEs
    • Filtering
    • Sorting
    Open problem →
  8. Problem 8Medium

    OFFSET Without LIMIT (Engine-Specific)

    Return employees starting from the 6th record onward.

    • Sorting
    • Top-N
    Open problem →
  9. Problem 9Medium

    Top N by a Calculated Expression

    Return the top 3 employees by annual salary (salary × 12).

    • CTEs
    • CASE expressions
    • Sorting
    Open problem →
  10. Problem 10Medium

    Pagination with a Multi-Column ORDER BY

    Return employees 4–6 sorted by department_id then salary descending.

    • CTEs
    • Sorting
    • Top-N
    Open problem →
  11. Problem 11Easy

    LIMIT 1 for the Single Latest Row

    Return the single highest-paid employee.

    • CTEs
    • Sorting
    • Top-N
    Open problem →
  12. Problem 12Medium

    Pagination with a Custom Page Size

    Return employees 3 and 4 (page 2 with a page size of 2).

    • CTEs
    • Sorting
    • Top-N
    Open problem →
  13. Problem 13Medium

    LIMIT with DISTINCT

    Return the first 3 distinct department IDs.

    • CTEs
    • Sorting
    • Top-N
    Open problem →
  14. Problem 14Medium

    Large OFFSET — The Performance Cost

    Return the last 2 employees by employee_id (skip the first 8).

    • CTEs
    • Sorting
    • Top-N
    Open problem →
  15. Problem 15Hard

    Pagination Capstone (Filter + Sort + Page + Tie-Breaker)

    Return page 2 (rows 4–6) of employees in department 1, sorted by salary descending.

    • CTEs
    • Filtering
    • Sorting
    Open problem →
  16. Problem 16Easy

    The "First" Trap — LIMIT Always Needs ORDER BY

    List 5 employees with employee_id 1-5. The natural-but-wrong way is to write `SELECT … LIMIT 5` and hope the engine returns the lowest IDs. The right way is to ORDER BY first. Show employee_id, first_name, last_name — sorted by employee_id ascending — first 5 only.

    • CTEs
    • Sorting
    • Top-N
    Open problem →
  17. Problem 17Medium

    Standard SQL: FETCH FIRST N ROWS ONLY

    Get the top 3 highest-paid employees. The ANSI-standard form is `FETCH FIRST … ROWS ONLY` (shown as a comment in the editor); the canonical solution here uses LIMIT for cross-engine compatibility. Tie-breaker: employee_id ascending. Show first_name, last_name, salary, employee_id.

    • Sorting
    • Top-N
    Open problem →
  18. Problem 18Easy

    SQL Server: TOP N

    Return the 5 employees with the LOWEST salary using SQL Server's `TOP N` syntax (write the canonical SQLite version with LIMIT here; the SQL Server variant is in solutionsByEngine). Tie-breaker: employee_id ascending. Show first_name, last_name, salary, employee_id.

    • CTEs
    • Sorting
    • Top-N
    Open problem →
  19. Problem 19Medium

    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
    Open problem →
  20. Problem 20Easy

    Bottom N — Lowest Values with ORDER BY ASC + LIMIT

    List the 3 LOWEST-paid employees with a deterministic tie-breaker. Show first_name, last_name, salary, employee_id.

    • CTEs
    • Sorting
    • Top-N
    Open problem →
  21. Problem 21Medium

    Compute the Total Number of Pages

    Pagination UIs need to know the total page count. Given a page size of 4 and the actual employee count, return the page metadata in a single row: total_rows, page_size, total_pages. Use CEIL/CEILING division so a partial last page still counts. Aliases must be EXACTLY: total_rows, page_size, total_pages.

    • Aggregation
    Open problem →
  22. Problem 22Hard

    Keyset Pagination — The Fix for Slow Deep Pages

    Instead of OFFSET, fetch the next page using the LAST seen employee_id from the previous page. Imagine the previous page ended at employee_id = 8. Get the next 4 employees with employee_id > 8, ordered by employee_id ascending. Show employee_id, first_name, last_name.

    • CTEs
    • Filtering
    • Sorting
    Open problem →
01

Attempt

Write a query from the brief before opening any hints.

02

Validate

Run it against the included dataset and inspect the output.

03

Explain

Compare the verified answer and explain each choice aloud.

Learn before practicing LIMIT & OFFSET

Strengthen your understanding with these targeted learning topics: