SQL ORDER BY Clause: Sort Query Results ASC and DESC

SQL Tutorial · Chapter 7 of 32

The SQL ORDER BY clause sorts the rows returned by a query by one or more columns, in ascending (ASC) or descending (DESC) order. Without ORDER BY, a database returns rows in whatever order is fastest, which can change from one run to the next. Add ORDER BY whenever the sequence of results matters.

SQL ORDER BY Syntax

SELECT column1, column2
FROM table_name
ORDER BY column1 [ASC | DESC], column2 [ASC | DESC];

ASC is the default and can be omitted. ORDER BY comes after WHERE, GROUP BY and HAVING, and before LIMIT.

Sample Table

Example 1: Sort Ascending (Default)

SELECT OrderID, Amount
FROM Orders
ORDER BY Amount;

Example 2: Sort Descending

DESC puts the largest values first, which is how you find the biggest orders.

SELECT OrderID, Product, Amount
FROM Orders
ORDER BY Amount DESC;

Example 3: Sort by Several Columns

Rows are sorted by the first column; ties are broken by the second. Here orders are grouped by customer, and within each customer the newest order comes first.

SELECT CustomerID, OrderID, OrderDate
FROM Orders
ORDER BY CustomerID ASC, OrderDate DESC;

Example 4: Sort Text and Sort by an Alias

Text sorts alphabetically. You can also sort by a calculated column using its alias, because ORDER BY runs after SELECT.

SELECT Product, Amount * 1.18 AS WithTax
FROM Orders
ORDER BY Product, WithTax DESC;

Example 5: Sort by a Column You Do Not Display

SELECT Product
FROM Orders
ORDER BY OrderDate DESC;

The sort column does not have to appear in the SELECT list, except when DISTINCT is used.

Works in MySQL, SQL Server, PostgreSQL, SQLite

• NULL placement: MySQL and SQLite sort NULLs first in ASC order; PostgreSQL and SQL Server differ (PostgreSQL treats NULL as largest, SQL Server as smallest). PostgreSQL and SQLite accept NULLS FIRST / NULLS LAST.
• Case: PostgreSQL and SQLite sort upper-case letters before lower-case; MySQL and SQL Server usually ignore case. Use ORDER BY LOWER(Name) for consistent results.
• Column position: ORDER BY 2 sorts by the second selected column in all four, but names are clearer.

Stable Sorting and Performance

When two rows have equal values in every ORDER BY column, the database is free to return them in any order, and that order can change between runs. For reports that must be reproducible, always finish the ORDER BY list with a unique column such as the primary key. This matters even more when ORDER BY is combined with LIMIT for pagination, because an unstable sort can show the same row on two pages and skip another entirely.

Sorting is one of the more expensive operations a database performs. If a query sorts a large table on the same column every time, an index on that column lets the engine read rows already in order and skip the sort step. Sorting on an expression such as LOWER(Name) defeats a plain index; PostgreSQL and SQL Server support indexes on expressions to fix that.

Common Mistakes

• Assuming rows come back in insertion order without ORDER BY. They may not.
• Putting ORDER BY before WHERE; clause order is fixed.
• Writing ORDER BY Amount, DESC with a stray comma. DESC belongs directly after the column.
• Sorting numbers stored as text, which places 1150 before 25 because ‘1’ < ‘2’. Store numbers as numeric types.

Practice Exercise

1. List OrderID and OrderDate with the oldest order first.
2. List Product and Amount sorted by product name A to Z, and for equal products by the cheapest first.

-- 1
SELECT OrderID, OrderDate FROM Orders ORDER BY OrderDate ASC;
-- 101, 102, 103, 104, 105, 106

-- 2
SELECT Product, Amount FROM Orders ORDER BY Product ASC, Amount ASC;
-- Headset 80, Keyboard 45, Laptop 1150, Laptop 1200, Monitor 300, Mouse 25

Related Chapters

• SQL TOP / LIMIT / FETCH FIRST – return only the first N sorted rows
• SQL WHERE – filter before you sort
• SQL Aliases – name the columns you sort by
• SQL from Scratch: full course overview

FAQ

What is the default sort order in SQL ORDER BY?

Ascending (ASC): smallest numbers, earliest dates and A-to-Z text first. Add DESC after a column name to reverse it.

Can I sort by more than one column?

Yes. List the columns separated by commas. The second column only decides the order of rows that have the same value in the first column.

Does ORDER BY slow down a query?

Sorting large result sets costs time and memory. An index on the sort column lets the database read rows already in order and removes most of that cost.

Chapter 7 of 32 · SQL from Scratch: all 32 chapters