ORDER BY & LIMIT
Overview
By default, SQL tables are chaotic. A SELECT query will return rows in completely random order (usually just the physical order they were inserted onto the hard drive). If you need data sorted (e.g., 'Show me the Highest Paid Employees' or 'Show me the Newest Accounts'), you MUST explicitly use ORDER BY. Because sorting millions of rows is expensive, you often pair it with LIMIT (or FETCH FIRST) to only return the Top 10 results, which is essential for Pagination.
Syntax
-- 1. Basic Ascending Sort (Default: A to Z, 0 to 9)
SELECT name, age FROM users
ORDER BY age ASC;
-- 2. Descending Sort (Z to A, Highest to Lowest)
SELECT name, salary FROM employees
ORDER BY salary DESC;
-- 3. Multi-Column Sorting (Sort by Dept first, THEN by Salary!)
SELECT department, name, salary
FROM employees
ORDER BY department ASC, salary DESC;
-- 4. LIMIT: The Top N Queries
-- Get the 5 highest-paid employees
SELECT name, salary FROM employees
ORDER BY salary DESC
LIMIT 5;
-- 5. OFFSET (Pagination!)
-- Skip the first 10, then grab the next 5 (Page 3)
SELECT name FROM products
ORDER BY id
LIMIT 5 OFFSET 10;Common Pitfalls
- Using
LIMITwithout anORDER BY. If you runSELECT * FROM users LIMIT 5;, the database will just grab the first 5 rows it bumps into on the hard drive. You will get different, random users every time you run it. ALWAYS useORDER BYwhen usingLIMITto guarantee deterministic results. - Deep Pagination.
OFFSET 100000 LIMIT 10is terribly slow. The database still has to manually scan and discard the first 100,000 rows before giving you the 10 you want. For massive tables, you must use 'Cursor-based Pagination' (e.g.,WHERE id > last_seen_id LIMIT 10).
Interview Questions
NULL values, where do they appear when you ORDER BY column ASC?It depends on the SQL dialect. In PostgreSQL and Oracle, NULL values are considered 'larger' than any other value, so they appear at the very bottom. In MySQL, they are considered 'smaller', appearing at the very top. You can explicitly fix this using ORDER BY column ASC NULLS LAST.
Real-World Example
Building the backend query for a Leaderboard UI showing the top 10 players.
SELECT player_handle, score, completed_at
FROM leaderboard
WHERE game_mode = 'ranked'
-- Sort by highest score. If scores tie, the one who finished earlier wins!
ORDER BY score DESC, completed_at ASC
LIMIT 10;Check Your Knowledge
Test your understanding of ORDER BY & LIMIT with these quick questions.