SQLFiltering Databeginner40 min read

Sorting Results with ORDER BY

Control the order of SQL query results using ORDER BY, ASC, DESC, multiple sorting columns, expressions, NULL positioning, and deterministic sorting for pagination.

In this lesson, you will learn

✓Explain why query results require ORDER BY for guaranteed ordering
✓Sort text, numeric values, and dates using ORDER BY
✓Use ASC and DESC to control sort direction
✓Combine multiple sort columns to create tie-breaking rules
✓Control the placement of NULL values with NULLS FIRST and NULLS LAST
✓Sort using expressions and selected-column aliases
✓Understand the risks of positional sorting such as ORDER BY 1
✓Build predictable sorting for LIMIT, OFFSET, and pagination
✓Recognize how indexes can help with sorting

1. Why Do We Need ORDER BY?

A database table does not have a guaranteed display order. Even if you insert rows in a particular sequence, a later query may return them in a different order because of indexes, query plans, updates, parallel execution, or other database operations.

ORDER BY tells the database how the result rows should be ordered. If you want the cheapest product first, the newest order first, or names alphabetically, specify that requirement explicitly.

Remember: WHERE decides which rows qualify. ORDER BY decides the order of the qualifying rows.

Unordered Results Versus Sorted Results

sql

1
2
3
4
5
6
7
8
-- The database does not guarantee a particular order
SELECT product_id, title, price
FROM products;
 
-- Sort products from lowest price to highest price
SELECT product_id, title, price
FROM products
ORDER BY price ASC;

The first query has no guaranteed row order. The second requests ascending order by price. If multiple products have the same price, their relative order is not guaranteed unless another sort expression breaks the tie.

2. Basic ORDER BY Syntax

The basic syntax places ORDER BY after FROM and any WHERE condition. You can sort by a column name and optionally specify ASC or DESC.

ASC means ascending order and is the default. DESC means descending order.

For numeric values, ascending normally means smallest to largest. For dates, it means earlier to later. For text, it follows the database's applicable collation rules.

Basic Sorting Syntax

sql

1
2
3
4
SELECT column1, column2
FROM table_name
WHERE condition
ORDER BY column1 ASC;

The WHERE clause is optional. The ASC keyword is also optional because ascending order is the default.

3. Ascending Order with ASC

Ascending order arranges values from low to high. For numbers, smaller values appear first. For dates, older dates appear first. For text, values follow the relevant collation's ordering rules.

ASC can be written explicitly to make the intended behavior clear, even though it is the default.

Cheapest Products First

sql

1
2
3
SELECT product_id, title, price
FROM products
ORDER BY price ASC;

Output

The products are displayed from the lowest price to the highest price. These rows are illustrative; actual results depend on your table data.

4. Descending Order with DESC

Descending order reverses the direction of ascending order. For numbers, the largest values appear first. For dates, the newest dates appear first.

DESC is commonly used for top-priced products, highest salaries, latest orders, and newest articles.

Most Expensive Products First

sql

1
2
3
SELECT product_id, title, price
FROM products
ORDER BY price DESC;

Output

The highest price appears first, followed by progressively lower prices.

5. Sorting Text Alphabetically

ORDER BY can sort text columns such as names, departments, product titles, and email addresses.

For simple English text, ascending order often looks like A to Z. However, exact text ordering depends on the database's collation, including its rules for case and language-specific characters.

Sort Customers by Name

sql

1
2
3
4
5
6
7
8
9
-- Alphabetical ordering according to the applicable collation
SELECT customer_id, full_name
FROM customers
ORDER BY full_name ASC;
 
-- Reverse the ordering
SELECT customer_id, full_name
FROM customers
ORDER BY full_name DESC;

The first query requests ascending text order. The second requests descending order. Do not assume that every language, accent, or uppercase/lowercase combination follows one universal ordering rule.

6. Sorting Dates and Timestamps

Dates and timestamps can be sorted chronologically. ASC places earlier values first, while DESC places later values first.

This is useful for showing the oldest orders, newest registrations, or most recently published articles. When the column stores timestamps, the sort compares timestamp values rather than the way those values happen to be displayed.

Oldest and Newest Orders

sql

1
2
3
4
5
6
7
8
9
-- Oldest orders first
SELECT order_id, customer_id, created_at
FROM orders
ORDER BY created_at ASC;
 
-- Newest orders first
SELECT order_id, customer_id, created_at
FROM orders
ORDER BY created_at DESC;

ASC requests chronological order from earlier to later. DESC requests reverse chronological order. If several orders share the same timestamp, add another sort expression for a predictable tie-breaker.

7. Sorting by Multiple Columns

You can specify multiple sorting expressions in a single ORDER BY clause. PostgreSQL sorts by the first expression. When two rows tie on that expression, it compares the second expression. If they also tie on the second expression, it continues to the next one.

Think of the first expression as the main rule and the remaining expressions as tie-breakers.

Each expression can have its own direction.

Sort by Department and Salary

sql

1
2
3
SELECT emp_id, first_name, department, salary
FROM employees
ORDER BY department ASC, salary DESC;

Output

Departments are sorted in ascending text order. Within each department, employees are sorted from highest salary to lowest salary. The output is illustrative.

8. A Step-by-Step Multi-Column Dry Run

Suppose employees must be grouped by department, then sorted by salary from highest to lowest. Imagine these input rows:

Engineering: Ana earns 90,000; Engineering: Ben earns 110,000; Finance: Cara earns 95,000; Finance: Dev earns 85,000.

First, compare department values. Rows in the same department are then compared by salary. The salary direction applies only after the department comparison is tied.

The Matching Query

sql

1
2
3
SELECT department, employee_name, salary
FROM employees
ORDER BY department ASC, salary DESC;

The database first sorts by department and then sorts salaries within each department. It does not globally sort every employee by salary, because department is the primary key for the ordering.

Illustrative Result

Department	Employee	Salary	Reason for Position
Engineering	Ben	110000	Engineering, highest salary
Engineering	Ana	90000	Engineering, lower salary
Finance	Cara	95000	Finance, highest salary
Finance	Dev	85000	Finance, lower salary

9. Sorting with WHERE and ORDER BY

Filtering and sorting solve different problems. WHERE determines which rows are included. ORDER BY determines the requested order of those rows.

For example, if you need active products ordered from highest price to lowest, first define the active-product condition and then specify the sort.

Filter Active Products and Sort Them

sql

1
2
3
4
SELECT product_id, title, price
FROM products
WHERE is_active = TRUE
ORDER BY price DESC;

Only active products qualify. Those qualifying products are then ordered by price from highest to lowest.

10. Controlling NULL Placement

NULL represents a missing or unknown value. It is not the same as zero, an empty string, or the text 'NULL'.

In PostgreSQL, the default ordering treats NULL as larger than non-NULL values. Consequently, NULL values appear last in ascending order and first in descending order.

You can override this behavior using NULLS FIRST or NULLS LAST. Explicitly specifying the desired position makes the query's intention clear.

Keep Missing Loyalty Tiers at the Bottom

sql

1
2
3
SELECT customer_id, full_name, loyalty_tier
FROM customers
ORDER BY loyalty_tier DESC NULLS LAST;

Output

The loyalty tier is sorted descending, while NULL values are explicitly placed last. The output is illustrative.

PostgreSQL Default NULL Placement

Sort Direction	Default NULL Placement
ASC	NULLS LAST
DESC	NULLS FIRST
ASC NULLS FIRST	NULL values first
DESC NULLS LAST	NULL values last

11. Sorting by an Expression

ORDER BY can sort by an expression, not just a stored column. This is useful when the requested ordering is based on a calculation.

For example, you can sort products by discounted price without storing a separate discounted-price column. You can also sort by the result of a function, provided the expression is valid for the data types involved.

Sort Products by Discounted Price

sql

1
2
3
4
5
6
7
8
SELECT
    product_id,
    title,
    price,
    discount_percent,
    price * (1 - discount_percent / 100.0) AS discounted_price
FROM products
ORDER BY price * (1 - discount_percent / 100.0) ASC;

The expression calculates a discounted price for sorting. This example assumes discount_percent is a numeric percentage between 0 and 100. The expression and output column depend on your schema.

12. Sorting by a SELECT Alias

A SELECT expression can have an output alias. In PostgreSQL, an output-column name can be referenced by ORDER BY.

This is useful when sorting by a calculated value, because the alias can make the query easier to read. Remember that an output alias is not a new column stored in the table.

Sort by the Calculated Alias

sql

1
2
3
4
5
6
SELECT
    product_id,
    title,
    price * 0.90 AS sale_price
FROM products
ORDER BY sale_price ASC;

The query calculates sale_price as 90% of price and orders the results by that output value. The alias exists in the query result, not as a permanent column in the products table.

13. Sorting by Column Position

SQL allows you to sort by the position of an output column in the SELECT list. For example, ORDER BY 1 sorts by the first selected output expression, while ORDER BY 2 sorts by the second.

Although this can be convenient for quick experiments, it is harder to maintain in application code. If someone reorders the SELECT list, the query may silently sort by a different value.

Prefer explicit column names or output aliases in production queries.

Positional Sorting Versus Explicit Sorting

sql

1
2
3
4
5
6
7
8
9
-- Sort by the second output column
SELECT id, email, created_at
FROM users
ORDER BY 2 DESC;
 
-- Clearer and easier to maintain
SELECT id, email, created_at
FROM users
ORDER BY email DESC;

Both queries sort by email in this particular SELECT list. The second query makes the intention explicit and is less fragile if the output columns change.

14. ORDER BY with LIMIT

LIMIT restricts how many rows a query returns. When you combine LIMIT with ORDER BY, you can request the first rows according to a particular sort order.

For example, to find the three most expensive products, sort prices in descending order and limit the result to three rows.

Without ORDER BY, LIMIT does not mean the first rows by price, creation time, or any other business rule. It only limits the rows returned from an unspecified ordering.

Find the Three Most Expensive Products

sql

1
2
3
4
SELECT product_id, title, price
FROM products
ORDER BY price DESC, product_id ASC
LIMIT 3;

The query sorts by price from highest to lowest, then uses product_id as a tie-breaker. LIMIT returns the first three rows in that requested order.

15. Deterministic Ordering and Pagination

Pagination divides a large result into smaller pages. A common approach uses LIMIT and OFFSET. For example, LIMIT 10 OFFSET 10 requests ten rows after skipping the first ten rows in the query's ordering.

For predictable ordering, use ORDER BY with enough expressions to uniquely identify each row. A unique primary key is often a useful final tie-breaker.

A deterministic ORDER BY makes the requested order unambiguous for a fixed dataset. However, concurrent inserts, deletes, or updates can still cause rows to shift between separate page requests. For highly consistent pagination, consider keyset pagination or an appropriate transaction strategy.

Page Through Customers

sql

1
2
3
4
5
6
7
8
9
10
11
-- First page
SELECT customer_id, full_name
FROM customers
ORDER BY full_name ASC, customer_id ASC
LIMIT 10 OFFSET 0;
 
-- Second page
SELECT customer_id, full_name
FROM customers
ORDER BY full_name ASC, customer_id ASC
LIMIT 10 OFFSET 10;

customer_id breaks ties between customers with identical names. This creates a unique ordering for a fixed dataset. Changes to the underlying data between requests can still affect OFFSET-based pages.

16. Keyset Pagination: A Brief Introduction

Keyset pagination uses the last row from the previous page as a starting point for the next page. It can avoid repeatedly skipping large numbers of rows.

For example, if results are ordered by customer_id ascending, the next page can request IDs greater than the last ID shown on the previous page. This assumes customer_id is a unique, consistently ordered key.

Fetch the Next Page by ID

sql

1
2
3
4
5
6
-- Suppose the previous page ended at customer_id 120
SELECT customer_id, full_name
FROM customers
WHERE customer_id > 120
ORDER BY customer_id ASC
LIMIT 10;

The query requests rows after ID 120 rather than skipping a fixed number of rows. For more complex sorting, the cursor condition must account for all sort columns and their directions.

17. How Indexes Can Help ORDER BY

Sorting can require work when the database must order many qualifying rows. An appropriate index may allow PostgreSQL to retrieve rows in the required order, sometimes avoiding a separate sort operation.

For example, an index on price may help with queries that order by price. A composite index can sometimes help with a multi-column ordering when its column order and directions match the query's requirements.

Index use is not guaranteed. The planner may decide that scanning the table and sorting the results is cheaper, especially when many rows must be returned. Use EXPLAIN to inspect the plan.

Inspect a Sort Query

sql

1
2
3
4
5
6
7
8
9
10
-- Inspect the planned operations
EXPLAIN
SELECT product_id, title, price
FROM products
ORDER BY price DESC
LIMIT 10;
 
-- A possible index for price ordering
CREATE INDEX idx_products_price
ON products (price);

The index may help with this ordering, but the query planner decides whether to use it. An index has storage and write-maintenance costs, so create indexes based on actual query needs.

18. Common Sorting Mistakes

Review these common problems before using ORDER BY in applications.

19. Guided Practice

Try each task before looking at the solutions. Assume the required tables and columns exist.

20. Guided Practice Solutions

Compare your solutions with the examples below.

Practice Solutions

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
-- Task 1: Cheapest products first
SELECT product_id, title, price
FROM products
ORDER BY price ASC;
 
-- Task 2: Highest salaries first
SELECT emp_id, first_name, salary
FROM employees
ORDER BY salary DESC;
 
-- Task 3: Department, then salary
SELECT emp_id, first_name, department, salary
FROM employees
ORDER BY department ASC, salary DESC;
 
-- Task 4: Five newest orders
SELECT order_id, customer_id, created_at
FROM orders
ORDER BY created_at DESC, order_id DESC
LIMIT 5;
 
-- Task 5: NULL tiers last
SELECT customer_id, full_name, loyalty_tier
FROM customers
ORDER BY loyalty_tier DESC NULLS LAST;
 
-- Task 6: Three cheapest active products
SELECT product_id, title, price
FROM products
WHERE is_active = TRUE
ORDER BY price ASC, product_id ASC
LIMIT 3;
 
-- Task 7: Sort by sale price
SELECT
    product_id,
    title,
    price,
    price * 0.90 AS sale_price
FROM products
ORDER BY sale_price ASC;
 
-- Task 8: Predictable customer ordering
SELECT customer_id, full_name
FROM customers
ORDER BY full_name ASC, customer_id ASC;

The solutions use explicit sort expressions and tie-breakers where appropriate. Actual results depend on the schema and stored data.

21. Multiple-Choice Questions

Test your understanding of sorting. Each answer includes a brief explanation.

22. SQL Interview Questions and Answers

Use these answers to revise the most important ORDER BY concepts.

23. Lesson Summary

ORDER BY controls the requested order of SQL query results. ASC sorts ascending and is the default; DESC sorts descending. Multiple expressions let you sort by a main criterion and then break ties using secondary criteria.

PostgreSQL places NULL values last by default in ascending order and first in descending order. Use NULLS FIRST or NULLS LAST when you need a particular placement. You can also sort by expressions and SELECT aliases.

For top-N queries and pagination, use explicit ordering and add a unique tie-breaker when necessary. Remember that concurrent data changes can still affect OFFSET-based pagination. Indexes can help with sorting, but the actual query plan should be inspected rather than assumed.

Common pitfalls to avoid

Real debugging lessons and exact syntax contrasts to avoid runtime bugs

#1 Assuming SQL returns rows in insertion order

Why it happens: Developers often see rows returned in a familiar order during testing and assume the database guarantees that sequence.

Incorrect Approach

sql

SELECT product_id, title, price
FROM products;

Correct Solution

sql

SELECT product_id, title, price
FROM products
ORDER BY product_id ASC;

Key takeaway: The first query does not guarantee row ordering. The second explicitly requests ascending order by product_id.

#2 Mixing up the priority of multiple sort expressions

Why it happens: Developers sometimes assume the last expression has the highest priority.

Incorrect Approach

sql

SELECT emp_id, department, salary
FROM employees
ORDER BY salary DESC, department ASC;

Correct Solution

sql

SELECT emp_id, department, salary
FROM employees
ORDER BY department ASC, salary DESC;

Key takeaway: The two queries express different sort hierarchies. The corrected example sorts by department first and salary within each department.

#3 Forgetting that NULL placement changes with direction

Why it happens: Different databases use different default NULL ordering rules, and developers may assume NULL always appears last.

Incorrect Approach

sql

SELECT customer_id, loyalty_tier
FROM customers
ORDER BY loyalty_tier DESC;

Correct Solution

sql

SELECT customer_id, loyalty_tier
FROM customers
ORDER BY loyalty_tier DESC NULLS LAST;

Key takeaway: In PostgreSQL, NULL values normally appear first for descending order. NULLS LAST explicitly places them at the end.

#4 Using ORDER BY positions without considering maintainability

Why it happens: ORDER BY 1 is concise but hides which output expression is being used.

Incorrect Approach

sql

SELECT id, email, created_at
FROM users
ORDER BY 2 DESC;

Correct Solution

sql

SELECT id, email, created_at
FROM users
ORDER BY email DESC;

Key takeaway: Explicit column names make the intent clearer and prevent a SELECT-list reorder from silently changing the sort criterion.

#5 Using LIMIT without specifying the required order

Why it happens: Developers expect LIMIT to return the highest, newest, or otherwise most important rows automatically.

Incorrect Approach

sql

SELECT product_id, title, price
FROM products
LIMIT 3;

Correct Solution

sql

SELECT product_id, title, price
FROM products
ORDER BY price DESC, product_id ASC
LIMIT 3;

Key takeaway: The corrected query specifies which products should rank first. The unique tie-breaker resolves equal prices for a fixed dataset.

Ready for hands-on challenges?

Test your skills with shuffled coding challenges in the Practice Arena.

Practice Arena →

Key takeaways

Without ORDER BY, SQL does not guarantee the order in which rows are returned.
ASC is the default sort direction.
DESC sorts values in descending order.
In multi-column sorting, each later expression breaks ties from the preceding expressions.
In PostgreSQL, ascending order defaults to NULLS LAST, while descending order defaults to NULLS FIRST.
NULLS FIRST and NULLS LAST explicitly control NULL placement.
A unique tie-breaker is important when stable ordering is required for pagination.
ORDER BY 1 sorts by the first output column, but explicit column names are generally easier to maintain.
An index may help satisfy ORDER BY, but the database optimizer chooses the execution plan.

Was this lesson helpful?

Found an error? Report an issue