LIMIT & OFFSET SQL practice problemMediumVerified answer

Pagination with a Multi-Column ORDER BY

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

  • CTEs
  • Sorting
  • Top-N

Interview brief

Understand the request

HR portal engineer A multi-sort paginated view ranks by department first, then salary, showing page 2.

With page size 3, return page 2 of employees sorted by department_id ascending, then salary descending, with employee_id as a final tie-breaker. Show first_name, last_name, department_id, salary, employee_id.

Return

  • Return first_name, last_name, department_id, and salary.

Constraints

  • Use a multi-column ORDER BY with LIMIT and OFFSET.

Data you will use

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

employees

  • first_nameTEXT
  • last_nameTEXT
  • department_idINTEGER
  • salaryDECIMAL
  • employee_idINTEGER

Hints, when you need them

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

Hint 1

Page 2 with page_size 3 → OFFSET = (2-1) * 3 = 3.

Hint 2

The ORDER BY must end with a unique column or the page boundaries (the 3rd/4th rows here) can flip on re-runs.

Hint 3

ORDER BY department_id, salary DESC, employee_id LIMIT 3 OFFSET 3

Verified SQL answer

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

Reveal solution and explanation
SELECT first_name, last_name, department_id, salary, employee_id FROM employees ORDER BY department_id, salary DESC, employee_id LIMIT 3 OFFSET 3;

Why this works

When the sort key has duplicates, OFFSET can split a tied group across two pages — and which row goes where is unpredictable. The unique tail of the ORDER BY (employee_id) prevents that.

Expected result

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

first_namelast_namedepartment_idsalaryemployee_id
JohnDoe1750001
AlexMiller17200011
DavidBrown1700005

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics: