KAHIBARO
10.8. ORDER BY
Table of Contents
Why `ORDER BY` Matters
When you query a database, the default order of rows is usually undefined. You might get rows in the order they were inserted, or in some internal order, but you must never rely on that.
If you care about the order of results, you must use ORDER BY.
Rule: If you need sorted results, always use ORDER BY.
Never assume the database returns rows in a meaningful order without it.
ORDER BY lets you sort your result set by one or more columns, in ascending or descending order, and even by expressions.
Basic `ORDER BY` Syntax
The general form is:
SELECT column1, column2, ...
FROM table_name
ORDER BY column_name [ASC | DESC];ASCmeans ascending order (smallest to largest, A to Z). It is the default.DESCmeans descending order (largest to smallest, Z to A).
Example with a simple users table:
users
+----+----------+-----------+--------+
| id | username | full_name | age |
+----+----------+-----------+--------+
| 1 | alice | Alice A | 30 |
| 2 | bob | Bob B | 25 |
| 3 | carol | Carol C | 35 |
+----+----------+-----------+--------+Sort by age ascending:
SELECT id, username, age
FROM users
ORDER BY age;Result:
+----+----------+-----+
| id | username | age |
+----+----------+-----+
| 2 | bob | 25 |
| 1 | alice | 30 |
| 3 | carol | 35 |
+----+----------+-----+Sort by age descending:
SELECT id, username, age
FROM users
ORDER BY age DESC;Sorting by Multiple Columns
You can sort by more than one column. This is useful when several rows share the same value in the first column.
Syntax:
SELECT ...
FROM table_name
ORDER BY column1 [ASC | DESC],
column2 [ASC | DESC],
...;Table example:
employees
+----+----------+----------+--------+
| id | name | dept | salary |
+----+----------+----------+--------+
| 1 | Alice | Sales | 50000 |
| 2 | Bob | Sales | 55000 |
| 3 | Carol | IT | 60000 |
| 4 | Dave | IT | 60000 |
| 5 | Eve | Sales | 45000 |
+----+----------+----------+--------+Sort by department ascending, then by salary descending inside each department:
SELECT name, dept, salary
FROM employees
ORDER BY dept ASC, salary DESC;Result:
+--------+--------+--------+
| name | dept | salary |
+--------+--------+--------+
| Carol | IT | 60000 |
| Dave | IT | 60000 |
| Bob | Sales | 55000 |
| Alice | Sales | 50000 |
| Eve | Sales | 45000 |
+--------+--------+--------+
dept controls the main grouping, salary DESC sorts within each department.
Another example, sort by salary ascending, then name ascending to stabilize the order:
SELECT name, dept, salary
FROM employees
ORDER BY salary ASC, name ASC;`ASC` vs `DESC` in Practice
You can mix ascending and descending in the same query.
Table example:
products
+----+----------+----------+--------+
| id | name | category | price |
+----+----------+----------+--------+
| 1 | Laptop | Tech | 900 |
| 2 | Mouse | Tech | 20 |
| 3 | Chair | Office | 70 |
| 4 | Desk | Office | 150 |
| 5 | Monitor | Tech | 200 |
+----+----------+----------+--------+Sort by category ascending, price descending:
SELECT name, category, price
FROM products
ORDER BY category ASC, price DESC;Result:
+---------+----------+-------+
| name | category | price |
+---------+----------+-------+
| Desk | Office | 150 |
| Chair | Office | 70 |
| Laptop | Tech | 900 |
| Monitor | Tech | 200 |
| Mouse | Tech | 20 |
+---------+----------+-------+
If you omit ASC or DESC, SQL uses ascending by default:
SELECT name, price
FROM products
ORDER BY price; -- same as ORDER BY price ASCSorting by Column Position
You can also sort by the position of a column in the SELECT list.
Syntax:
SELECT col1, col2, col3
FROM table_name
ORDER BY 2, 3 DESC;
Here, 2 means ordered by the second selected column, and 3 DESC means third selected column in descending order.
Example:
SELECT name, category, price
FROM products
ORDER BY 2, 3 DESC;
This orders by category (the second selected column), and then by price (the third) descending inside each category.
Recommendation: Sorting by column position works, but using column names is clearer and less error prone.
Use column positions only when you have a strong reason, such as quick ad hoc queries.
Sorting With Expressions
You are not limited to column names in ORDER BY. You can sort by expressions and calculated values.
Common examples:
- Sort by a computed discount price
ORDER BY price * (1 - discount_rate) - Sort by the length of a string
ORDER BY LENGTH(name) - Sort by a case expression
Example: Sorting by a Computed Column
Table:
orders
+----+----------+----------+--------+
| id | item | quantity | price |
+----+----------+----------+--------+
| 1 | Pen | 10 | 1.5 |
| 2 | Notebook | 3 | 4.0 |
| 3 | Bag | 1 | 25.0 |
+----+----------+----------+--------+Sort by total cost (quantity times price):
SELECT id, item, quantity, price, quantity * price AS total_cost
FROM orders
ORDER BY quantity * price DESC;Result:
+----+----------+----------+-------+------------+
| id | item | quantity | price | total_cost |
+----+----------+----------+-------+------------+
| 3 | Bag | 1 | 25.0 | 25.0 |
| 2 | Notebook | 3 | 4.0 | 12.0 |
| 1 | Pen | 10 | 1.5 | 15.0 |
+----+----------+----------+-------+------------+Note: The order of id 1 and 2 here will depend on the actual computed totals. Always check your math.
Example: Using the Alias in `ORDER BY`
Many databases let you use the alias defined in the SELECT list:
SELECT id, item, quantity * price AS total_cost
FROM orders
ORDER BY total_cost DESC;This is easier to read than repeating the expression.
Example: Sorting With `CASE`
Suppose you have priorities: "high", "medium", "low". You want "high" first, then "medium", then "low", even though alphabetically "high" comes before "low" and "medium".
tasks
+----+---------+----------+
| id | title | priority |
+----+---------+----------+
| 1 | Task A | low |
| 2 | Task B | high |
| 3 | Task C | medium |
+----+---------+----------+
Custom order with CASE:
SELECT id, title, priority
FROM tasks
ORDER BY
CASE priority
WHEN 'high' THEN 1
WHEN 'medium' THEN 2
WHEN 'low' THEN 3
ELSE 4
END;Result:
+----+---------+----------+
| id | title | priority |
+----+---------+----------+
| 2 | Task B | high |
| 3 | Task C | medium |
| 1 | Task A | low |
+----+---------+----------+This pattern is common when you need custom business ordering.
Sorting With `NULL` Values
When your data contains NULL, databases must decide where to place those rows in an ordered list.
By default:
- Many databases, such as PostgreSQL, sort
NULLvalues first in ascending order and last in descending order. - Others, such as some configurations of MySQL, may treat
NULLdifferently.
To control this, you can usually use NULLS FIRST or NULLS LAST in ORDER BY.
Table:
users
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | Alice | 30 |
| 2 | Bob | NULL |
| 3 | Carol | 25 |
| 4 | Dave | NULL |
+----+----------+------+Ascending by age, with nulls last:
SELECT id, name, age
FROM users
ORDER BY age ASC NULLS LAST;Possible result:
+----+-------+------+
| id | name | age |
+----+-------+------+
| 3 | Carol | 25 |
| 1 | Alice | 30 |
| 2 | Bob | NULL |
| 4 | Dave | NULL |
+----+-------+------+Descending by age, with nulls first:
SELECT id, name, age
FROM users
ORDER BY age DESC NULLS FIRST;
Important: Handling NULL correctly is critical.
Always think about where rows with missing values should appear in sorted results, and use NULLS FIRST or NULLS LAST if needed.
If your database does not support NULLS FIRST / LAST, you can simulate it with expressions, for example:
ORDER BY (age IS NULL), age
Since (age IS NULL) is false (0) or true (1), non null ages come first.
`ORDER BY` With `LIMIT` (Top N Results)
ORDER BY becomes especially powerful when combined with LIMIT (or TOP in some SQL engines) to get only the first N rows.
Common patterns:
- Get the 10 most recent posts.
- Get the 5 highest paid employees.
- Get the cheapest 3 products.
Example table:
posts
+----+-----------------+---------------------+
| id | title | created_at |
+----+-----------------+---------------------+
| 1 | Hello World | 2023-01-01 10:00:00 |
| 2 | Second Post | 2023-01-02 09:00:00 |
| 3 | Another Post | 2023-01-03 12:00:00 |
| 4 | Newest Post | 2023-01-04 08:00:00 |
+----+-----------------+---------------------+Latest 2 posts:
SELECT id, title, created_at
FROM posts
ORDER BY created_at DESC
LIMIT 2;Result:
+----+-------------+---------------------+
| id | title | created_at |
+----+-------------+---------------------+
| 4 | Newest Post | 2023-01-04 08:00:00 |
| 3 | Another Post| 2023-01-03 12:00:00 |
+----+-------------+---------------------+Top 3 most expensive products:
SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 3;This pattern is extremely common in backend development, for example when you display "Top 10" lists or recent activity.
Using `ORDER BY` With `GROUP BY` and Aggregates
When you use GROUP BY, you often want to sort by an aggregate value, such as COUNT, SUM, or AVG.
Table:
sales
+----+----------+--------+
| id | product | amount |
+----+----------+--------+
| 1 | Pen | 10 |
| 2 | Pen | 5 |
| 3 | Notebook | 7 |
| 4 | Bag | 20 |
| 5 | Notebook | 8 |
+----+----------+--------+Total sales per product, highest first:
SELECT product, SUM(amount) AS total_amount
FROM sales
GROUP BY product
ORDER BY total_amount DESC;Result:
+----------+--------------+
| product | total_amount |
+----------+--------------+
| Bag | 20 |
| Notebook | 15 |
| Pen | 15 |
+----------+--------------+
If there is a tie in total_amount, the order between those rows is undefined unless you add a secondary sort:
ORDER BY total_amount DESC, product ASC;Common Patterns and Examples
Here is a compact table of common sorting tasks and how to write them.
| Goal | Example ORDER BY clause |
|---|---|
| Alphabetical A to Z | ORDER BY name ASC |
| Alphabetical Z to A | ORDER BY name DESC |
| Lowest price first | ORDER BY price ASC |
| Highest price first | ORDER BY price DESC |
| Newest record first | ORDER BY created_at DESC |
| Oldest record first | ORDER BY created_at ASC |
| Sort by multiple fields | ORDER BY category ASC, price DESC |
| Custom priority order | ORDER BY CASE priority WHEN 'high' THEN 1 ... END |
| Shortest name first | ORDER BY LENGTH(name) ASC |
| Longest name first | ORDER BY LENGTH(name) DESC |
| Non null values first | ORDER BY (age IS NULL), age ASC |
| Nulls last, descending score | ORDER BY score DESC NULLS LAST |
| Top N by score | ORDER BY score DESC LIMIT 10 |
| Sort by aggregate (most orders) | ORDER BY COUNT(*) DESC with GROUP BY customer_id |
Summary
ORDER BYdefines the order of rows in your query results.- Without
ORDER BYthe order is not guaranteed, so never rely on it. - You can sort by:
- Single or multiple columns, each with
ASCorDESC. - Column positions from the
SELECTlist, although names are clearer. - Expressions and aliases, including calculations and
CASEexpressions. - Always think about how to handle
NULLvalues, and useNULLS FIRSTorNULLS LASTor equivalent patterns when necessary. ORDER BYbecomes very powerful in combination withLIMITand aggregate functions for top N queries and reports.
Views: 8