Sorting Results with ORDER BY
Introduction
You've learned to SELECT data and filter it with WHERE. But often, you want results in a specific order—alphabetically, by date, by price, etc. The ORDER BY clause lets you sort query results exactly how you need them.
The ORDER BY Clause
Basic Syntax
SELECT column1, column2
FROM table_name
ORDER BY column_name;
Your First ORDER BY Query
SELECT * FROM users ORDER BY age;
Output:
id | name | email | age
----+--------------+----------------------+-----
3 | Carol White | carol@example.com | 22
1 | Alice Smith | alice@example.com | 28
4 | Dave Brown | dave@example.com | 28
2 | Bob Jones | bob@example.com | 34
5 | Eve Davis | eve@example.com | 34
Results are sorted by age from youngest to oldest (ascending order—the default).
Sort Direction
ASC - Ascending (Default)
SELECT * FROM users ORDER BY age ASC;
-- ASC is optional (it's the default)
SELECT * FROM users ORDER BY age; -- Same result
Output: 22, 28, 28, 34, 34 (smallest to largest)
DESC - Descending
SELECT * FROM users ORDER BY age DESC;
Output:
id | name | email | age
----+--------------+----------------------+-----
2 | Bob Jones | bob@example.com | 34
5 | Eve Davis | eve@example.com | 34
1 | Alice Smith | alice@example.com | 28
4 | Dave Brown | dave@example.com | 28
3 | Carol White | carol@example.com | 22
Results go from oldest to youngest (34, 34, 28, 28, 22).
Sorting by Text
Alphabetical Order
SELECT name, email FROM users ORDER BY name;
Output:
name | email
---------------+----------------------
Alice Smith | alice@example.com
Bob Jones | bob@example.com
Carol White | carol@example.com
Dave Brown | dave@example.com
Eve Davis | eve@example.com
Names sorted alphabetically (A → Z).
Reverse Alphabetical
SELECT name FROM users ORDER BY name DESC;
Output:
name
---------------
Eve Davis
Dave Brown
Carol White
Bob Jones
Alice Smith
Names sorted Z → A.
Sorting by Multiple Columns
Primary and Secondary Sort
SELECT name, age
FROM users
ORDER BY age, name;
How it works:
- First, sort by age (ascending)
- Then, for rows with the same age, sort by name (ascending)
Output:
name | age
---------------+-----
Carol White | 22
Alice Smith | 28 ← Age 28
Dave Brown | 28 ← Age 28 (sorted alphabetically within same age)
Bob Jones | 34 ← Age 34
Eve Davis | 34 ← Age 34 (sorted alphabetically)
Different Directions per Column
SELECT name, age
FROM users
ORDER BY age DESC, name ASC;
- Age: Descending (oldest first)
- Name: Ascending (A → Z within same age)
Output:
name | age
---------------+-----
Bob Jones | 34 ← Oldest
Eve Davis | 34 ← (B comes before E)
Alice Smith | 28
Dave Brown | 28 ← (A comes before D)
Carol White | 22 ← Youngest
Sorting by Expressions
Calculated Values
SELECT
name,
age,
age * 12 AS age_in_months
FROM users
ORDER BY age * 12 DESC;
Sort by the calculated value (age in months).
String Functions
SELECT name
FROM users
ORDER BY LENGTH(name);
Output:
name
---------------
Bob Jones (9 chars)
Eve Davis (9 chars)
Alice Smith (11 chars)
Dave Brown (10 chars)
Carol White (11 chars)
Sorted by name length (shortest to longest).
Sorting by Column Position
You can reference columns by their position in the SELECT list:
SELECT name, age FROM users
ORDER BY 2; -- Order by 2nd column (age)
Equivalent to:
SELECT name, age FROM users
ORDER BY age;
Warning: This is less readable. Use column names for clarity.
NULL Values in Sorting
Default Behavior
NULL values appear last in ascending order, first in descending order (in PostgreSQL):
-- Add a user with NULL age
INSERT INTO users (name, email) VALUES ('Grace Lee', 'grace@example.com');
SELECT name, age FROM users ORDER BY age;
Output:
name | age
---------------+-----
Carol White | 22
Alice Smith | 28
Dave Brown | 28
Bob Jones | 34
Eve Davis | 34
Grace Lee | NULL ← NULL appears last
NULLS FIRST / NULLS LAST
Control NULL placement explicitly:
-- NULLs first
SELECT name, age FROM users
ORDER BY age NULLS FIRST;
-- NULLs last
SELECT name, age FROM users
ORDER BY age NULLS LAST;
Combining WHERE and ORDER BY
SELECT name, age
FROM users
WHERE age >= 25
ORDER BY age DESC;
Process order:
- Filter rows with WHERE (age >= 25)
- Sort remaining rows with ORDER BY (descending)
Output:
name | age
---------------+-----
Bob Jones | 34
Eve Davis | 34
Alice Smith | 28
Dave Brown | 28
LIMIT with ORDER BY
Get the "top N" or "bottom N" results:
Top 3 Oldest Users
SELECT name, age
FROM users
ORDER BY age DESC
LIMIT 3;
Output:
name | age
-------------+-----
Bob Jones | 34
Eve Davis | 34
Alice Smith | 28
Top 3 Youngest Users
SELECT name, age
FROM users
ORDER BY age ASC
LIMIT 3;
Practical Examples
Example 1: Customer Report (Alphabetical)
SELECT
name AS customer_name,
email AS contact_email
FROM users
WHERE age >= 21
ORDER BY name ASC;
Example 2: Age Leaderboard (Oldest First)
SELECT
name,
age,
age || ' years old' AS age_display
FROM users
ORDER BY age DESC, name ASC;
Example 3: Email Domain Sorting
SELECT
name,
email,
SUBSTRING(email FROM POSITION('@' IN email) + 1) AS domain
FROM users
ORDER BY domain, name;
Sorts by email domain, then by name.
Example 4: Recent Records First
For tables with timestamps:
SELECT *
FROM orders
ORDER BY created_at DESC
LIMIT 10;
Shows 10 most recent orders.
Performance Considerations
Indexes Speed Up Sorting
-- Create index on age column
CREATE INDEX idx_users_age ON users(age);
-- Now this query is faster:
SELECT * FROM users ORDER BY age;
Avoid Sorting Large Result Sets
-- ❌ Slow: Sorts millions of rows
SELECT * FROM huge_table ORDER BY created_at DESC;
-- ✅ Faster: Filter first, then sort
SELECT * FROM huge_table
WHERE created_at >= '2024-01-01'
ORDER BY created_at DESC;
Use LIMIT
-- ❌ Sorts all rows, returns 10
SELECT * FROM users ORDER BY age LIMIT 10;
-- ✅ Database can optimize (only needs top 10)
SELECT * FROM users ORDER BY age LIMIT 10;
Good news: Databases optimize ORDER BY ... LIMIT automatically!
Common Mistakes
Mistake 1: Forgetting ORDER BY
-- Results might come back in any order!
SELECT * FROM users LIMIT 5;
-- Always specify order for predictable results
SELECT * FROM users ORDER BY id LIMIT 5;
Mistake 2: Sorting by Non-Selected Columns
-- This works but can be confusing
SELECT name FROM users ORDER BY age;
Results are sorted by age, but you can't see it in the output.
Better:
SELECT name, age FROM users ORDER BY age;
Mistake 3: Wrong ASC/DESC
-- Want newest first, but used ASC
SELECT * FROM orders ORDER BY order_date ASC;
-- ✅ Correct
SELECT * FROM orders ORDER BY order_date DESC;
Practice Exercises
Exercise 1
Sort all users by name alphabetically.
Solution
SELECT * FROM users ORDER BY name;
Exercise 2
Find the 3 youngest users.
Solution
SELECT name, age FROM users
ORDER BY age ASC
LIMIT 3;
Exercise 3
Sort users by age (oldest first), then by name (A-Z) for users with the same age.
Solution
SELECT name, age FROM users
ORDER BY age DESC, name ASC;
Exercise 4
Find users over 25, sorted by age (youngest to oldest).
Solution
SELECT * FROM users
WHERE age > 25
ORDER BY age ASC;
Exercise 5
Sort users by email length (shortest first).
Solution
SELECT name, email, LENGTH(email) AS email_length
FROM users
ORDER BY LENGTH(email);
Key Takeaways
- ✅ ORDER BY sorts query results
- ✅ ASC: Ascending (default) - smallest to largest, A to Z
- ✅ DESC: Descending - largest to smallest, Z to A
- ✅ Multiple columns: Sort by first column, then second (for ties)
- ✅ Combine with WHERE: Filter, then sort
- ✅ Combine with LIMIT: Get top/bottom N results
- ✅ NULL handling: NULLS FIRST / NULLS LAST
Next Steps
Congratulations! You've completed Module 2 and can now:
- Set up PostgreSQL
- Write SELECT queries
- Filter data with WHERE
- Sort results with ORDER BY
In Module 3, we'll learn how to modify data using INSERT, UPDATE, and DELETE. You'll go beyond reading data and learn to create, update, and remove records.
Keep up the great work!