SQL ORDER BY, LIMIT & OFFSET — Sorting and Paging Results

Why sort and limit matter

A SELECT without ORDER BY returns rows in an undefined order — the database is free to return them however it pleases, and that order can change between runs as data grows or indexes are rebuilt. Any result set shown to a user, exported to a file, or compared in a test needs an explicit ORDER BY.

LIMIT (and its SQL-standard equivalent FETCH FIRST) caps how many rows come back. Together, ORDER BY + LIMIT are the building blocks of ranked lists, dashboards, and paginated APIs.

ORDER BY basics

-- Newest orders first
SELECT id, customer_id, total_amount, created_at
FROM orders
ORDER BY created_at DESC;

-- Alphabetically by last name, then first name
SELECT id, first_name, last_name
FROM customers
ORDER BY last_name ASC, first_name ASC;

-- Cheapest in-stock products first
SELECT product_name, unit_price, stock_quantity
FROM products
WHERE stock_quantity > 0
ORDER BY unit_price ASC;

ASC (ascending, A→Z, 0→9) is the default and can be omitted. Columns not in SELECT can still appear in ORDER BY — the sort happens before projection.

Multi-column sorts

Each column in ORDER BY gets its own direction. The database sorts by the first column, then breaks ties using the second, and so on.

-- High-value orders on top; within same total, most recent first
SELECT id, customer_id, total_amount, created_at
FROM orders
ORDER BY total_amount DESC, created_at DESC;

-- Product grid: category ascending, then price ascending within category
SELECT product_name, category, unit_price
FROM products
ORDER BY category ASC, unit_price ASC;

NULL ordering

NULL is neither greater than nor less than any value. Where NULLs sort depends on the database:

Postgres / Oracle: NULLs sort last in ASC, first in DESC.
MySQL / SQL Server: NULLs sort first in ASC.

Use explicit NULLS FIRST / NULLS LAST (Postgres, Oracle) or a CASE workaround (MySQL, SQL Server) to control this:

-- Postgres: tasks with no due date sink to the bottom
SELECT id, title, due_date
FROM tasks
ORDER BY due_date ASC NULLS LAST;

-- MySQL equivalent (CASE pushes NULLs to position 1 → last)
SELECT id, title, due_date
FROM tasks
ORDER BY CASE WHEN due_date IS NULL THEN 1 ELSE 0 END, due_date ASC;

LIMIT and OFFSET

LIMIT n returns at most n rows. OFFSET k skips the first k rows. Together they paginate a result set.

-- Top 10 best-selling products
SELECT product_id, SUM(quantity) AS units_sold
FROM order_items
GROUP BY product_id
ORDER BY units_sold DESC
LIMIT 10;

-- Page 3 of a customer list (page size = 20)
SELECT id, email, created_at
FROM customers
ORDER BY created_at DESC
LIMIT 20 OFFSET 40;  -- skip pages 1 and 2

SQL Server / Oracle syntax:

-- SQL Server
SELECT TOP 10 * FROM orders ORDER BY total_amount DESC;

-- SQL:2008 standard (Postgres, SQL Server 2012+, Oracle 12c+)
SELECT * FROM orders ORDER BY total_amount DESC
FETCH FIRST 10 ROWS ONLY;

-- With offset
OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY;

The OFFSET pagination problem

OFFSET is convenient but has two problems at scale:

Performance: the database reads and discards the first k rows on every request. Page 500 of 20 items means discarding 9 980 rows.
Drift: if a row is inserted or deleted between page requests, items shift — the user sees a duplicate or skips a row.

Keyset (cursor) pagination — the fix

Instead of skipping rows by count, remember the last seen value and filter from there. This is O(log n) with an index instead of O(n).

-- First page: 20 newest orders
SELECT id, created_at, customer_id, total_amount
FROM orders
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Next page: pass the last row's (created_at, id) as the cursor
SELECT id, created_at, customer_id, total_amount
FROM orders
WHERE (created_at, id) < ('2026-06-15 10:23:00', 5831)
ORDER BY created_at DESC, id DESC
LIMIT 20;

The id tiebreaker ensures stable ordering when two rows share the same created_at. The index on (created_at DESC, id DESC) makes this fast at any page depth.

ORDER BY with expressions and CASE

You can sort by an expression or computed value directly in ORDER BY:

-- Sort by absolute value of profit margin (closest to zero first)
SELECT product_name, (revenue - cost) AS profit
FROM products
ORDER BY ABS(revenue - cost) ASC;

-- Custom priority: 'urgent' tickets first, then by created_at
SELECT id, title, priority, created_at
FROM support_tickets
ORDER BY CASE priority
           WHEN 'urgent'   THEN 1
           WHEN 'high'     THEN 2
           WHEN 'medium'   THEN 3
           ELSE 4
         END,
         created_at ASC;

Recap

Always pair ORDER BY with LIMIT — an unsorted limit is meaningless. Use NULLS LAST (or the CASE workaround) when nullable sort columns would push NULLs to the wrong end. Replace OFFSET-based pagination with keyset pagination for any list that grows beyond a few hundred rows — it stays fast regardless of page depth and avoids row-drift bugs.

More ways to practice