Level 6 · Page three of the staff directory

Paging combines two ideas from this chapter: a fully defined sort and an offset. This directory adds a twist: the sort keys go in different directions.

Every key has its own direction

DESC is not a setting for the whole ORDER BY. It belongs to the column right before it, and every column without a keyword is ascending:

SELECT department, hire_date, employee_id
FROM employees
ORDER BY department ASC, hire_date DESC, employee_id ASC;

Rows are sorted by department first. Only among people in the same department does hire_date DESC decide, newest first. Only if two people share both would employee_id decide. In Harbor & Pine’s table no two people in one department share a hire date, so the last key never kicks in today, but it guarantees the order stays fixed when someone new joins.

Pages are offsets

With 10 rows per page, page n starts after (n - 1) * 10 rows:

LIMIT 10 OFFSET 20   -- rows 21 to 30, page 3

Here that page starts with Eric Lopez (Engineering, hired 2023-08-21), runs through Catherine Hale and Robert Kim in Executive, and ends with four Finance employees, the newest being Jack Sanchez (hired 2024-05-03).

Common mistakes

• Expecting ORDER BY department, hire_date DESC to sort departments descending. It does not; only hire_date is descending.
• Changing the sort between pages. Page three only makes sense if pages one and two used the exact same ORDER BY.
• Using the page number as the offset, OFFSET 3. Offsets count rows, not pages.
• Leaving out the tie-breaker because “there are no ties today”. Paging is where a missing tie-breaker hurts most: if two rows tie at the edge of a page, one person can show up on both pages and another on neither, and nobody notices until HR gets a complaint.
• Forgetting that a large offset still reads every skipped row. For a 60-person directory that is nothing, but apps that page through millions of rows switch to “keyset” paging: remember the last row shown and ask for rows after it.

In SQL Server

SQL Server pages with OFFSET ... FETCH NEXT, and it requires an ORDER BY:

SELECT employee_id, department, hire_date
FROM employees
ORDER BY department ASC, hire_date DESC, employee_id ASC
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;