Paging with OFFSET

Apps show long lists one page at a time. LIMIT sets the page size, and OFFSET skips the rows before the page.

Page 1, rows 1 to 5:

SELECT first_name, salary FROM employees
ORDER BY salary DESC
LIMIT 5;

Page 2, rows 6 to 10:

SELECT first_name, salary FROM employees
ORDER BY salary DESC
LIMIT 5 OFFSET 5;

The formula: OFFSET = (page - 1) × page size.

Always sort when you page. Without ORDER BY, the database can return rows in any order, so someone could show up on two pages or on none.

Sort by something unique, too. Emily and Christopher both earn 140,000 and sit right on the break between pages 1 and 2. Adding id as a tie-breaker settles who goes first:

SELECT first_name, salary FROM employees
ORDER BY salary DESC, id
LIMIT 5 OFFSET 5;