Topic 20 of 52
ORDER BY ASC/DESC
Overview
ORDER BY sorts query results in ascending or descending order. Without ORDER BY, the database returns rows in an undefined order (which can change between queries). Sorting by indexed columns is much faster.
Syntax
sql
-- Ascending (default)
SELECT * FROM products ORDER BY price;
SELECT * FROM products ORDER BY price ASC;
-- Descending
SELECT * FROM products ORDER BY price DESC;
-- Multiple columns (sort by first, then break ties with second)
SELECT * FROM employees ORDER BY department ASC, salary DESC;
-- Sort by alias (defined in SELECT)
SELECT name, price * 1.18 AS price_with_tax
FROM products
ORDER BY price_with_tax DESC;
-- Sort by column position (not recommended — brittle)
SELECT name, price FROM products ORDER BY 2 DESC;
-- NULL handling in sort
ORDER BY last_login DESC NULLS LAST; -- NULLs go at bottom
ORDER BY last_login ASC NULLS FIRST; -- NULLs go at top (default for ASC)Common Pitfalls
- ORDER BY without LIMIT can be very slow on large tables — the database must sort all rows before returning any.
- For pagination to work correctly, ORDER BY must include a unique column (like id) as the final sort criterion.
- Interview tip: NULLS FIRST / NULLS LAST controls where NULL values appear in the sorted output — critical for proper ranking queries.
Real-World Example
Leaderboard and paginated product listing:
example
sql
-- Sales leaderboard: top salespeople this month
SELECT
s.name,
COUNT(o.id) AS orders_closed,
SUM(o.amount) AS total_revenue,
AVG(o.amount) AS avg_deal_size,
MAX(o.amount) AS biggest_deal
FROM salespeople s
JOIN orders o ON s.id = o.salesperson_id
WHERE DATE_TRUNC('month', o.closed_at) = DATE_TRUNC('month', CURRENT_DATE)
GROUP BY s.id, s.name
ORDER BY total_revenue DESC, orders_closed DESC
LIMIT 10;
-- Paginated product listing (consistent sort for pagination)
SELECT id, name, price, created_at
FROM products
WHERE is_active = TRUE
ORDER BY created_at DESC, id DESC -- secondary sort by id ensures stable pagination
LIMIT 20 OFFSET 40; -- page 3 (items 41-60)