Introduction
By default, a relational database does not guarantee any particular order for the rows returned by a query. The ORDER BY clause lets you explicitly sort the result set by one or more columns, in ascending or descending order.
Cricket analogy: Without ORDER BY, a scorecard's batting order might list players in whatever order they were entered, but adding ORDER BY runs ensures you always see the top scorer first, regardless of default storage order.
Syntax
SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC | DESC], column2 [ASC | DESC];Explanation
ASC (ascending) is the default sort direction, so it can be omitted; DESC sorts in descending order. When multiple columns are listed, the result set is sorted by the first column, and ties are broken using subsequent columns in the order they are listed. ORDER BY is applied after WHERE filtering and typically executes near the end of query processing, so it can also reference column aliases defined in the SELECT list.
Cricket analogy: Sorting batters by runs DESC, and breaking ties by strike_rate DESC, is like ranking a run-chase performance first by total runs, then by strike rate whenever two players scored the same number of runs.
Example
-- employees table: id, first_name, last_name, department, salary, hire_date
SELECT first_name, last_name, department, salary
FROM employees
ORDER BY department ASC, salary DESC;Output
Rows are grouped alphabetically by department, and within each department the highest-paid employees appear first, for example: 'Engineering | 120000', 'Engineering | 95000', 'Marketing | 88000', ... down to the lowest salary in the last department alphabetically.
Cricket analogy: Grouping employees by department alphabetically, then by descending salary within each, is like listing teams alphabetically at a tournament and, within each squad, ranking players from highest to lowest match fee.
Key Takeaways
- Row order is never guaranteed without an explicit ORDER BY clause.
- ASC is the default direction; use DESC for descending order.
- Multiple sort keys break ties using the order columns are listed.
- ORDER BY can reference column aliases and, in most databases, column position numbers.
Practice what you learned
1. What is the default sort direction in an ORDER BY clause if none is specified?
2. Given ORDER BY department ASC, salary DESC, how are ties in department resolved?
3. Which clause should ORDER BY typically follow in a SELECT query?
4. Is row order guaranteed by default without ORDER BY?
Was this page helpful?
You May Also Like
Filtering with WHERE
Use the WHERE clause to filter rows returned by a query based on specified conditions.
LIMIT and Pagination
Restrict the number of rows returned and implement pagination using LIMIT and OFFSET.
SELECT Statement Basics
Learn how to retrieve data from a table using the SELECT statement, the foundation of every SQL query.