ORDER BY, LIMIT & OFFSET
BeginnerORDER BY sorts result rows; LIMIT restricts the count returned; OFFSET skips rows — together they enable pagination, but naive OFFSET pagination degrades at scale.
Overview
ORDER BY sorts the final result set by one or more columns (ASC by default, DESC explicitly). Without ORDER BY, the row order is non-deterministic — the DB may return rows in any order including a different order on repeated identical queries. LIMIT n returns the first n rows after sorting. OFFSET k skips the first k rows, which causes the DB to scan and discard all k rows on every page request — making OFFSET-based pagination O(n) per page at large offsets. Keyset (cursor) pagination using WHERE id > last_seen_id is O(1) per page and is the correct approach for deep pagination in product systems.
ORDER BY and LIMIT Basics
Sorting by multiple columns creates a tie-breaking order. NULL values sort last in ASC order in PostgreSQL (NULLS LAST is the default for ASC). Always include a deterministic tiebreaker (like id) in ORDER BY to guarantee stable pagination.
-- Sort by salary descending, then by name ascending as tiebreaker
SELECT id, full_name, salary, department_id
FROM employees
ORDER BY salary DESC, full_name ASC;
-- NULLs: PostgreSQL default is NULLS LAST for ASC, NULLS FIRST for DESC
-- Make it explicit to avoid surprises
SELECT id, full_name, commission
FROM employees
ORDER BY commission DESC NULLS LAST; -- NULLs go to the bottom
-- LIMIT: top 5 highest-paid employees
SELECT id, full_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 5;
-- MySQL LIMIT with OFFSET (page 3, 10 rows per page)
SELECT id, email, created_at
FROM users
ORDER BY created_at DESC, id DESC -- always include unique tiebreaker!
LIMIT 10 OFFSET 20;Keyset (Cursor) Pagination vs OFFSET
OFFSET pagination scans all preceding rows on every request, causing O(page_number) DB work. Keyset pagination uses a WHERE condition on the last seen key, keeping each page O(1) regardless of depth.
-- OFFSET pagination (anti-pattern for deep pages)
-- Page 1000 with 20 rows/page = DB scans 19,980 rows and discards them
SELECT id, email, created_at
FROM users
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 19980; -- gets slower as offset grows!
-- Keyset (cursor) pagination — fast for any depth
-- First page
SELECT id, email, created_at
FROM users
ORDER BY created_at DESC, id DESC
LIMIT 20;
-- Returns last row: created_at='2024-06-01 10:00:00', id=4231
-- Next page: use last seen values as cursor
SELECT id, email, created_at
FROM users
WHERE (created_at, id) < ('2024-06-01 10:00:00', 4231) -- row tuple comparison
ORDER BY created_at DESC, id DESC
LIMIT 20;
-- Spring Data JPA: Pageable for OFFSET-based (simple use cases)
Page<User> findByStatus(String status, Pageable pageable);
// Service layer:
Pageable page = PageRequest.of(0, 20, Sort.by("createdAt").descending());
Page<User> users = userRepo.findByStatus("active", page);Key Points to Remember
- 1Without ORDER BY, row order is non-deterministic — never rely on implicit ordering.
- 2Always include a unique tiebreaker (id) in ORDER BY when paginating.
- 3OFFSET pagination is O(n) per page — performance degrades linearly with page depth.
- 4Keyset (cursor) pagination uses WHERE on the last seen value — O(1) per page at any depth.
- 5PostgreSQL: NULLS FIRST / NULLS LAST controls NULL sort position explicitly.
- 6Spring Data Pageable uses OFFSET internally — switch to keyset for high-page-count APIs.
Interview Questions
Sign in to ask AriaWhat is the problem with OFFSET-based pagination at scale?
Explain keyset pagination and when you would use it.
How does ORDER BY NULL handling differ between PostgreSQL and MySQL?
Implement cursor-based pagination for a feed of orders in Spring Boot.
Ask Aria about ORDER BY, LIMIT & OFFSET
Your personal AI tutor — ask anything about this concept
Revision Status
Personal Notes
Sign in to save personal notes for this topic.
Discussion
Sign in to join the discussion.