ORDER BY and LIMIT
ORDER BY sorts the result, and LIMIT keeps the first rows of it. Without ORDER BY, rows come
back in no fixed order.
Syntax
Order of execution: FROM → WHERE → SELECT → DISTINCT → ORDER BY → LIMIT / OFFSET
ORDER BY runs after SELECT, so it can use a column alias. LIMIT runs last, so it cuts
the result only after it has been sorted.
CodeSELECT column1, column2, ... FROM table_name WHERE condition ORDER BY column1 [ASC | DESC] [NULLS FIRST | NULLS LAST], column2 ... LIMIT n OFFSET m;
| Part | Meaning |
|---|---|
ASC | smallest first: 0 to 9, A to Z, old to new (the default) |
DESC | largest first |
column1, column2 | sort by column1; column2 only orders rows that tie on column1 |
NULLS FIRST / NULLS LAST | where missing values go (not in MySQL or SQL Server) |
LIMIT n | keep the first n rows |
OFFSET m | skip m rows first |
LIMIT works in PostgreSQL and MySQL. The SQL standard writes OFFSET m ROWS FETCH FIRST n ROWS ONLY, which PostgreSQL, Oracle and SQL Server accept; SQL Server also has SELECT TOP n.
Examples
Sorting
Put rows in order: cheapest first, newest first, A to Z, or grouped by one column and then another.
CodeSELECT product_name, price FROM products ORDER BY price; -- cheapest first SELECT product_name, price FROM products ORDER BY price DESC; -- priciest first -- By category, priciest first within each SELECT category_id, product_name, price FROM products ORDER BY category_id, price DESC;
Top N
Get just the first few rows of a ranking, such as the 3 most expensive products.
CodeSELECT product_name, price FROM products ORDER BY price DESC LIMIT 3;
Ties
Get the same order on every run when some rows have equal values.
Code-- With equal prices, PostgreSQL can return the tied rows in either order SELECT product_name, price FROM products ORDER BY price LIMIT 3; -- A tie-breaker fixes the order SELECT product_name, price FROM products ORDER BY price, product_name LIMIT 3;
NULLs
Decide whether rows with missing values go at the top or the bottom. PostgreSQL and Oracle treat
NULL as the largest value; MySQL and SQL Server treat it as the smallest, so there NULLs come first
with ASC.
Code-- In PostgreSQL, NULLs come last with ASC and first with DESC SELECT full_name, date_of_birth FROM customers ORDER BY date_of_birth DESC; -- missing birth dates on top SELECT full_name, date_of_birth FROM customers ORDER BY date_of_birth DESC NULLS LAST; -- missing birth dates at the end
Pages
Show a long list a page at a time, like a product catalog.
Code-- 5 per page: page p skips (p - 1) * 5 rows SELECT product_id, product_name FROM products ORDER BY product_name LIMIT 5; -- page 1 SELECT product_id, product_name FROM products ORDER BY product_name LIMIT 5 OFFSET 5; -- page 2
Remember
DESCapplies only to the column it follows.- Add a tie-breaker, such as a name or an id, whenever values can be equal, especially with
LIMIT. LIMITandOFFSETcome afterORDER BY.- Page p is
LIMIT size OFFSET (p - 1) * size.
Practice
- Five Cheapest Products You Can Buy (Easy)
- Page 3 of the Catalog (Easy)
- Top Rated Under ₹2,000 (Easy)