How Does ORDER BY Work in SQL?
Learn how ORDER BY works in SQL to sort results ascending or descending, use multiple columns for tie-breaking, handle NULLs and build top-N queries.
Expected Interview Answer
ORDER BY sorts the rows returned by a query based on one or more columns or expressions, in ascending (ASC, the default) or descending (DESC) order. It is the only reliable way to control the order of a result set.
You can sort by multiple columns, where later columns break ties from earlier ones, and you can sort by column positions, aliases, or expressions. ORDER BY runs logically after SELECT and WHERE, so it can use SELECT aliases. NULLs sort together and their position depends on the database, controllable with NULLS FIRST or NULLS LAST where supported. It is typically the last clause, often paired with LIMIT or OFFSET for top-N results.
- Guarantees a predictable row order
- Supports multi-column tie-breaking sorts
- Allows ascending or descending per column
- Works with expressions and column aliases
- Pairs with LIMIT for top-N queries
AI Mentor Explanation
Think of ranking a batting leaderboard at the end of a season. ORDER BY runs_scored DESC lines players up from most to fewest runs, and adding batting_average DESC as a second key breaks ties so two players on equal runs are separated by average, giving a clean, predictable order just like a printed scorecard.
Step-by-Step Explanation
Step 1
Choose the sort column
Pick the column or expression whose order you want, such as price or created_at.
Step 2
Set the direction
Use ASC for ascending (the default) or DESC for descending after the column name.
Step 3
Add tie-breakers
List additional columns separated by commas; each resolves ties left by the previous ones.
Step 4
Handle NULLs
Use NULLS FIRST or NULLS LAST where supported to control where missing values land.
Step 5
Combine with LIMIT
Add LIMIT or OFFSET after ORDER BY to fetch top-N or paginated results reliably.
What Interviewer Expects
- Knowing ASC is the default direction
- Ability to sort by multiple columns
- Understanding tie-breaking with secondary keys
- Awareness that without ORDER BY row order is not guaranteed
- Knowing ORDER BY can use aliases and expressions
Common Mistakes
- Assuming rows come back sorted without ORDER BY
- Thinking DESC is the default instead of ASC
- Forgetting that ORDER BY applies to the whole result, not per column independently
- Confusing ORDER BY with GROUP BY
- Ignoring database-specific NULL sort ordering
Best Answer (HR Friendly)
“ORDER BY is how you tell SQL to sort your results, either smallest to largest or the reverse. You can sort by several columns so ties are broken sensibly, and it is the only dependable way to control the order rows come back in.”
Code Example
-- Ascending is the default
SELECT name, salary
FROM employees
ORDER BY salary;
-- Descending, highest salary first
SELECT name, salary
FROM employees
ORDER BY salary DESC;
-- Multi-column: department ascending, then salary descending as a tie-breaker
SELECT name, department, salary
FROM employees
ORDER BY department ASC, salary DESC;
-- Top 5 earners using ORDER BY with LIMIT
SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 5;Follow-up Questions
- What is the default sort direction in ORDER BY?
- How do you sort by more than one column?
- Where do NULL values appear in a sorted result?
- Can you use a column alias in ORDER BY?
- How does ORDER BY combine with LIMIT for top-N queries?
MCQ Practice
1. What is the default sort direction in ORDER BY?
ORDER BY sorts in ascending order (ASC) by default unless DESC is specified.
2. In ORDER BY department ASC, salary DESC, what does the salary key do?
Secondary sort keys resolve ties left by earlier keys, so salary orders rows sharing the same department.
3. Without an ORDER BY clause, what is the order of returned rows?
SQL does not guarantee any row order unless ORDER BY is specified.
Flash Cards
What is the default ORDER BY direction? — Ascending (ASC).
How do you break ties in a sort? — List additional columns after commas; each resolves ties left by earlier columns.
Is row order guaranteed without ORDER BY? — No. Without ORDER BY the order of returned rows is unspecified.
How do you get top-N rows? — Combine ORDER BY with LIMIT (or FETCH FIRST) to return the first N sorted rows.