SQL 22 🛢️ Sorting Results with ORDER BY

A SELECT statement returns rows. Without an ORDER BY clause, the order of those rows is undefined. The database may return them in the order they were inserted, in the order they were found on disk, or in an order determined by the query plan. The order is not guaranteed, and it can change between runs or between database versions. The ORDER BY clause is the only way to guarantee a specific order.

The previous chapters covered filtering — comparison operators, logical operators, range, set, pattern, and NULL handling. This chapter covers sorting. Sorting is not just a presentation concern. It determines which rows are returned by LIMIT and OFFSET, it affects the order of aggregation, and it controls which row a TOP 1 query returns.

Key point: The ORDER BY clause is the only way to guarantee the order of rows in a result set. Without it, the database is free to return the rows in any order. The order may appear stable in testing and change in production. Never rely on an unordered result set. If order matters, use ORDER BY.

---

Why the ORDER BY Clause Matters

A result set without an explicit order is a set, not a list. The database can return the rows in any sequence. The sequence is an implementation detail, not a promise.

The presentation problem. A user list should appear alphabetically. An order list should appear by date, newest first. A product list should appear by price, lowest first. The ORDER BY clause produces the order the application needs.

The pagination problem. The LIMIT and OFFSET clauses return a specific number of rows starting from a specific position. Without ORDER BY, the position is undefined. Page 1 and page 2 may contain overlapping or missing rows. The ORDER BY clause makes the pagination deterministic.

The top-N problem. The query “the five most recent orders” requires ORDER BY order_date DESC LIMIT 5. Without the ORDER BY, the LIMIT 5 returns an arbitrary five rows. The ORDER BY clause makes the top-N query meaningful.

The aggregation problem. Some aggregate functions and window functions depend on the order of the rows. STRING_AGG and ARRAY_AGG can take an ORDER BY clause inside the aggregate. Window functions always use an ORDER BY clause.

The trade-off. Sorting has a cost. The database must read the rows, sort them, and return them in order. For a large result set, the sort may spill to disk. An index on the sort column can eliminate the sort for simple cases. The ORDER BY clause is the price of a deterministic order.

---

a. The Basic ORDER BY Clause

The ORDER BY clause comes after the WHERE clause and before the LIMIT and OFFSET clauses. The syntax is:

SELECT columns
FROM table
WHERE condition
ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ...

The ASC keyword means ascending order — smallest to largest, A to Z, oldest to newest. The DESC keyword means descending order — largest to smallest, Z to A, newest to oldest. The default is ASC. The ASC keyword is optional.

SELECT * FROM customers ORDER BY last_name;
SELECT * FROM customers ORDER BY last_name ASC;
SELECT * FROM orders ORDER BY total DESC;
SELECT * FROM employees ORDER BY hire_date DESC;

The first two queries are equivalent. They sort customers by last name in ascending order. The third sorts orders by total in descending order. The fourth sorts employees by hire date in descending order, newest first.

The ORDER BY clause can reference multiple columns. The sort is applied to the first column, then to the second column for rows where the first column is equal, and so on.

SELECT * FROM customers ORDER BY last_name ASC, first_name ASC;
SELECT * FROM orders ORDER BY customer_id ASC, order_date DESC;

The first query sorts customers by last name, then by first name within each last name. The second sorts orders by customer, then by date within each customer, newest first.

The ORDER BY clause can reference a column that is not in the SELECT list.

SELECT first_name, last_name FROM customers ORDER BY customer_id;

The query returns only the first and last names, but sorts by the customer_id. The sort column does not need to appear in the output.

---

b. Sorting by Expressions and Aliases

The ORDER BY clause accepts expressions, not just column names. The expression is evaluated for each row, and the result is used as the sort key.

SELECT * FROM customers ORDER BY LOWER(last_name);
SELECT * FROM products ORDER BY price * quantity;
SELECT * FROM orders ORDER BY EXTRACT(YEAR FROM order_date), order_date;

The first query sorts customers by the lowercase version of the last name. It is case-insensitive. The second sorts products by the total value of the line item. The third sorts orders by year, then by the full date within each year.

The ORDER BY clause can reference an alias defined in the SELECT clause. Most databases support this. The alias is a shorthand for the expression.

SELECT
    product_id,
    price * quantity AS total_value
FROM order_items
ORDER BY total_value DESC;

The query sorts by the total_value alias, which is the price * quantity expression. The alias is defined in the SELECT clause and used in the ORDER BY clause.

The ORDER BY clause can also use a column position — a number that refers to the position of a column in the SELECT list.

SELECT first_name, last_name, city FROM customers ORDER BY 2, 1;

The query sorts by the second column (last_name), then by the first column (first_name). Positional references are supported by most databases, but they are fragile. Adding or removing a column from the SELECT list changes the meaning of the position. Use column names or aliases instead.

---

c. Sorting NULL Values

NULL values have a defined position in the sort order. In most databases, NULL is considered smaller than any non-null value. In ascending order, NULLs appear first. In descending order, NULLs appear last.

SELECT * FROM customers ORDER BY city ASC;
-- NULL cities appear first

SELECT * FROM customers ORDER BY city DESC;
-- NULL cities appear last

The behavior is not universal. PostgreSQL considers NULL larger than any non-null value by default. Oracle and MySQL consider NULL smaller. SQL Server considers NULL smaller than any non-null value for sorting but treats NULL as equal to NULL for comparison. The behavior depends on the database.

The NULLS FIRST and NULLS LAST clauses explicitly control the position of NULL values. These clauses are supported by PostgreSQL, Oracle, and SQL Server. MySQL does not support them.

SELECT * FROM customers ORDER BY city ASC NULLS LAST;
SELECT * FROM customers ORDER BY city DESC NULLS FIRST;

The first query sorts customers by city in ascending order, but places NULL cities last. The second sorts by city in descending order, but places NULL cities first.

Database	NULL in ASC	NULL in DESC
PostgreSQL	Last	First
Oracle	Last	First
MySQL	First	Last
SQL Server	First	Last

The COALESCE function can be used to control the position of NULL values in a portable way.

SELECT * FROM customers ORDER BY COALESCE(city, 'ZZZ') ASC;

The query sorts customers by city, but treats NULL cities as the string 'ZZZ', which sorts last in ascending order. The COALESCE function provides a portable alternative to NULLS FIRST and NULLS LAST.

---

Complete Example Session

This session demonstrates the ORDER BY clause on a small table.

-- ============================================
-- PART 1: THE TABLE
-- ============================================

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name  VARCHAR(50) NOT NULL,
    last_name   VARCHAR(50) NOT NULL,
    department  VARCHAR(50),
    salary      DECIMAL(10, 2),
    hire_date   DATE NOT NULL
);

INSERT INTO employees VALUES
    (1, 'Alice', 'Johnson', 'Engineering', 95000, '2020-01-15'),
    (2, 'Bob', 'Smith', 'Sales', 65000, '2019-03-20'),
    (3, 'Carol', 'Williams', 'Engineering', 85000, '2021-06-10'),
    (4, 'Dave', 'Brown', NULL, 70000, '2022-09-05'),
    (5, 'Eve', 'Davis', 'Marketing', NULL, '2023-02-28'),
    (6, 'Frank', 'Miller', 'Sales', 72000, '2018-11-12');

-- ============================================
-- PART 2: BASIC ASCENDING SORT
-- ============================================

SELECT * FROM employees ORDER BY last_name ASC;

-- Output:
-- Brown, Davis, Johnson, Miller, Smith, Williams

-- ============================================
-- PART 3: BASIC DESCENDING SORT
-- ============================================

SELECT * FROM employees ORDER BY salary DESC;

-- Output:
-- Johnson (95000), Williams (85000), Miller (72000),
-- Brown (70000), Smith (65000), Davis (NULL)

-- Davis appears last because NULL is the largest value in PostgreSQL.

-- ============================================
-- PART 4: MULTIPLE COLUMNS
-- ============================================

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

-- Output:
-- Engineering: Johnson (95000), Williams (85000)
-- Marketing: Davis (NULL)
-- Sales: Miller (72000), Smith (65000)
-- NULL department: Brown (70000)

-- ============================================
-- PART 5: SORTING BY AN EXPRESSION
-- ============================================

SELECT
    employee_id,
    first_name,
    last_name,
    salary
FROM employees
ORDER BY salary * 12 DESC;

-- Sorts by the annual salary, highest first.

-- ============================================
-- PART 6: SORTING BY AN ALIAS
-- ============================================

SELECT
    employee_id,
    salary,
    salary * 12 AS annual_salary
FROM employees
ORDER BY annual_salary DESC;

-- Sorts by the annual_salary alias.

-- ============================================
-- PART 7: SORTING BY A COLUMN NOT IN THE SELECT
-- ============================================

SELECT first_name, last_name FROM employees ORDER BY hire_date DESC;

-- Sorts by the hire date, newest first.
-- The hire date is not in the output.

-- ============================================
-- PART 8: NULLS FIRST AND NULLS LAST
-- ============================================

SELECT * FROM employees ORDER BY salary ASC NULLS FIRST;

-- Davis (NULL) appears first.

SELECT * FROM employees ORDER BY salary DESC NULLS LAST;

-- Davis (NULL) appears last.

-- ============================================
-- PART 9: THE COALESCE PORTABLE ALTERNATIVE
-- ============================================

SELECT * FROM employees ORDER BY COALESCE(salary, 0) ASC;

-- Davis (NULL) appears first because COALESCE(salary, 0) = 0.

SELECT * FROM employees ORDER BY COALESCE(salary, 999999999) DESC;

-- Davis (NULL) appears last because COALESCE(salary, 999999999) is the largest value.

-- ============================================
-- PART 10: THE SUMMARY
-- ============================================

-- ORDER BY sorts the result set
-- ASC is ascending (default)
-- DESC is descending
-- Multiple columns sort hierarchically
-- Expressions and aliases are allowed
-- NULL position depends on the database
-- NULLS FIRST and NULLS LAST control the position

The ten parts cover the table, basic ascending sort, basic descending sort, multiple columns, sorting by an expression, sorting by an alias, sorting by a column not in the select, NULLS FIRST and NULLS LAST, the COALESCE portable alternative, and the summary.

---

Quick Reference

The ORDER BY Syntax

Element	Purpose
ORDER BY col	Sort by a column
ASC	Ascending (default)
DESC	Descending
ORDER BY col1, col2	Sort by multiple columns
ORDER BY expression	Sort by an expression
ORDER BY alias	Sort by a select alias
NULLS FIRST	Place nulls first
NULLS LAST	Place nulls last

The NULL Position by Database

Database	ASC	DESC
PostgreSQL	Last	First
Oracle	Last	First
MySQL	First	Last
SQL Server	First	Last

The Clause Order

Clause	Purpose
SELECT	Columns to return
FROM	Table
WHERE	Filter rows
GROUP BY	Group rows
HAVING	Filter groups
ORDER BY	Sort rows
LIMIT	Limit rows
OFFSET	Skip rows

---

Best Practices

✅ Do This:

-- Use ORDER BY for deterministic results
SELECT * FROM customers ORDER BY last_name                    -- ✅

-- Use multiple columns for hierarchical sort
SELECT * FROM orders ORDER BY customer_id, order_date DESC    -- ✅

-- Use NULLS LAST for portable null handling
SELECT * FROM customers ORDER BY city ASC NULLS LAST          -- ✅

-- Use COALESCE for portability
SELECT * FROM customers ORDER BY COALESCE(city, 'ZZZ') ASC    -- ✅

-- Use ORDER BY with LIMIT for top-N queries
SELECT * FROM orders ORDER BY total DESC LIMIT 10             -- ✅

❌ Don’t Do This:

-- Don't rely on an unordered result
SELECT * FROM customers LIMIT 10  -- no order                  -- ❌

-- Don't use positional references
SELECT first_name, last_name FROM customers ORDER BY 1, 2     -- ⚠️

-- Don't use functions on indexed columns
SELECT * FROM customers ORDER BY LOWER(last_name)             -- ⚠️

-- Don't assume NULL position
SELECT * FROM customers ORDER BY city                          -- ⚠️

---

Common Pitfalls

Pitfall	Why It Happens	Fix
Nondeterministic order	No ORDER BY	Add the clause
Pages overlap	No ORDER BY with LIMIT/OFFSET	Add the clause
NULL position wrong	Database-specific behavior	Use NULLS FIRST/LAST
Index not used	Function on sort column	Sort by the column directly
Slow sort	Large result set	Use an index or reduce the set

---

Real-World Examples

1. Sort by Name

SELECT * FROM customers ORDER BY last_name, first_name;

2. Sort by Date Descending

SELECT * FROM orders ORDER BY order_date DESC;

3. Sort by Price Ascending

SELECT * FROM products ORDER BY price ASC;

4. Top 10 by Total

SELECT * FROM orders ORDER BY total DESC LIMIT 10;

5. Pagination

SELECT * FROM customers ORDER BY customer_id LIMIT 20 OFFSET 40;

6. Sort by Expression

SELECT * FROM products ORDER BY price * quantity DESC;

7. Sort by Alias

SELECT price * quantity AS total FROM order_items ORDER BY total DESC;

8. NULLS LAST

SELECT * FROM customers ORDER BY city ASC NULLS LAST;

9. NULLS FIRST

SELECT * FROM customers ORDER BY city DESC NULLS FIRST;

10. Portable NULL Handling

SELECT * FROM customers ORDER BY COALESCE(city, 'ZZZ') ASC;

---

Visual

The Clause Order

┌──────────────────────────────────────────────┐
│  SELECT ...                                  │
│  FROM ...                                    │
│  WHERE ...                                   │
│  GROUP BY ...                                │
│  HAVING ...                                  │
│  ORDER BY ...   ← sorts the result           │
│  LIMIT ...                                   │
│  OFFSET ...                                  │
│                                              │
│  ORDER BY comes after WHERE and before LIMIT.│
│                                              │
└──────────────────────────────────────────────┘

The Sort Direction

┌──────────────────────────────────────────────┐
│  ASC (default)                               │
│    1, 2, 3, ..., A, B, C, ..., oldest, newest│
│                                              │
│  DESC                                        │
│    ..., 3, 2, 1, ..., C, B, A, ..., newest, oldest│
│                                              │
└──────────────────────────────────────────────┘

The NULL Position

┌──────────────────────────────────────────────┐
│  NULL POSITION BY DATABASE                   │
│                                              │
│  PostgreSQL:                                 │
│    ASC → NULLS LAST                          │
│    DESC → NULLS FIRST                        │
│                                              │
│  MySQL:                                      │
│    ASC → NULLS FIRST                         │
│    DESC → NULLS LAST                         │
│                                              │
│  Use NULLS FIRST/LAST for explicit control.  │
│                                              │
└──────────────────────────────────────────────┘

The Top-N Query

┌──────────────────────────────────────────────┐
│  TOP-N QUERY                                 │
│                                              │
│  SELECT * FROM orders                        │
│  ORDER BY total DESC                         │
│  LIMIT 10;                                   │
│                                              │
│  Without ORDER BY, the LIMIT returns an      │
│  arbitrary 10 rows.                          │
│                                              │
│  With ORDER BY, the LIMIT returns the        │
│  10 largest orders.                          │
│                                              │
└──────────────────────────────────────────────┘

---

Summary

Item	Value
ORDER BY	Sorts the result set
ASC	Ascending (default)
DESC	Descending
Multiple columns	Sort hierarchically
Expression	Sort by a computed value
Alias	Sort by a select alias
NULLS FIRST	Place nulls first
NULLS LAST	Place nulls last
Clause order	After WHERE, before LIMIT
Without ORDER BY	Order is undefined

Key takeaways:

• The ORDER BY clause is the only way to guarantee the order of rows. Without it, the database returns the rows in an arbitrary order. The order may appear stable but is not guaranteed .
• The ASC keyword is the default. The DESC keyword reverses the order. The ASC keyword is optional. The DESC keyword is required for descending order .
• Multiple columns sort hierarchically. The first column is the primary sort key. The second column is the tiebreaker for rows where the first column is equal. The pattern continues for additional columns .
• The ORDER BY clause accepts expressions and aliases. The expression is evaluated for each row. The alias is a shorthand for an expression in the SELECT list. The sort column does not need to appear in the output .
• NULL values have a defined position. The position depends on the database. PostgreSQL and Oracle place NULLs last in ascending order. MySQL and SQL Server place NULLs first. Use NULLS FIRST or NULLS LAST for explicit control .
• The COALESCE function provides portable NULL handling. The COALESCE(city, 'ZZZ') expression treats NULL cities as the string 'ZZZ', which sorts last in ascending order. The approach works in every database .
• The ORDER BY clause is required for top-N queries and pagination. Without it, the LIMIT and OFFSET clauses return an arbitrary subset. With it, the query is deterministic and the pages do not overlap .

Remember: The ORDER BY clause is the only way to guarantee the order of a result set. ASC is the default. DESC reverses the order. Multiple columns sort hierarchically. Expressions and aliases are allowed. NULL position depends on the database. Use NULLS FIRST or NULLS LAST for explicit control. Use ORDER BY with LIMIT and OFFSET for deterministic pagination. Never rely on an unordered result.

---

Stop using slow, ad-bloated tool sites! 🤮

🔎 Search “KandZ Tools” on Google to use many professional utilities for free.

KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)

⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free

🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!