sql / intermediate
Snippet
Deterministic Result Paging with OFFSET and FETCH NEXT
Standard ANSI SQL pagination uses OFFSET to skip a specific number of preceding rows and FETCH NEXT to restrict the returned row count. Ordering by a unique column secondary key ensures deterministic page boundaries.
snippet.sql
sql
1
2
3
4
5
SELECT product_id, product_name, unit_priceFROM productsORDER BY unit_price DESC, product_id ASCOFFSET 20 ROWSFETCH NEXT 10 ROWS ONLY;
Breakdown
1
SELECT product_id, product_name, unit_price
Specifies product attributes required for the current display page.
2
FROM products
Identifies the source product table.
3
ORDER BY unit_price DESC, product_id ASC
Establishes strict deterministic sorting, essential for accurate row positioning across pages.
4
OFFSET 20 ROWS
Bypasses the first 20 records (pages 1 and 2 if page size is 10).
5
FETCH NEXT 10 ROWS ONLY;
Restricts the output set size to exactly 10 rows for page 3.