LIMIT & OFFSET SQL practice problemHardVerified answer

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
  • Top-N

Interview brief

Understand the request

HR systems developer The production pagination system needs department-filtered, salary-sorted pages with offset control.

Build a paginated view of IT and Sales employees only (department_id IN (1, 3)). Sort by hire_date descending with employee_id ascending as tie-breaker. With page size 2, return page 3. Show first_name, last_name, hire_date, department_id, employee_id.

Return

  • Return first_name, last_name, department_id, and salary.

Constraints

  • Combine WHERE, ORDER BY, LIMIT, and OFFSET in one query.

Data you will use

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

employees

  • first_nameTEXT
  • last_nameTEXT
  • hire_dateDATE
  • department_idINTEGER
  • employee_idINTEGER

Hints, when you need them

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

Hint 1

Tackle one clause at a time: WHERE narrows; ORDER BY orders the narrowed set; LIMIT/OFFSET selects a page.

Hint 2

The tie-breaker matters because hire_date can repeat; without it, page 3 may show different rows on different runs.

Hint 3

WHERE department_id IN (1, 3) ORDER BY hire_date DESC, employee_id LIMIT 2 OFFSET 4

Verified SQL answer

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

Reveal solution and explanation
SELECT first_name, last_name, hire_date, department_id, employee_id FROM employees WHERE department_id IN (1, 3) ORDER BY hire_date DESC, employee_id LIMIT 2 OFFSET 4;

Why this works

This is the standard production pagination shape: filter, sort with deterministic tail, and page. Real systems also memoise the total count for the page-count calculation (covered in Q21) and may switch to keyset pagination for deep pages (Q22).

Expected result

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

first_namelast_namehire_datedepartment_idemployee_id
RachelGarcia2020-11-30312
ChrisAnderson2020-04-2539

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics: