SQL ORDER BY

SQL ORDER BY sorts the rows of a result set. Without it, the database may return rows in any order, so whenever order matters you should add ORDER BY.

By default sorting is ascending (A to Z, smallest to largest). Add DESC for descending order.

Sort Ascending

List products from cheapest to most expensive.

SELECT ProductName, Price
FROM Products
ORDER BY Price;

ProductName    | Price
---------------+------
Dark Chocolate | 4.25
Green Tea      | 6
Wheat Flour    | 8
Honey          | 11.5
Basmati Rice   | 12.5
Almonds        | 15
Olive Oil      | 18.75
Coffee Beans   | 22
(8 rows)

Sort Descending

Use DESC to show the largest stock first.

SELECT ProductName, Stock
FROM Products
ORDER BY Stock DESC;

ProductName    | Stock
---------------+------
Dark Chocolate | 500
Green Tea      | 300
Wheat Flour    | 200
Basmati Rice   | 120
Coffee Beans   | 80
Almonds        | 60
Olive Oil      | 45
Honey          | 0
(8 rows)

ORDER BY Multiple Columns

When the first column has equal values, the second column decides. Here customers are sorted by country, then by name within each country.

SELECT Country, CustomerName
FROM Customers
ORDER BY Country ASC, CustomerName DESC;

Country  | CustomerName
---------+---------------
Canada   | Jade Imports
Pakistan | Golden Grains
Pakistan | Blue Sky Foods
Pakistan | Alpha Traders
Spain    | Delta Supplies
UAE      | Evergreen Co
UK       | Fresh Mart
UK       | Cedar Market
USA      | Indigo Retail
USA      | Harbor Goods
(10 rows)

Sorting by an Expression or Alias

Unlike WHERE, ORDER BY runs after SELECT, so you can sort by an alias.

SELECT ProductName, Price * Stock AS StockValue
FROM Products
ORDER BY StockValue DESC;

ProductName    | StockValue
---------------+-----------
Dark Chocolate | 2125
Green Tea      | 1800
Coffee Beans   | 1760
Wheat Flour    | 1600
Basmati Rice   | 1500
Almonds        | 900
Olive Oil      | 843.75
Honey          | 0
(8 rows)

How NULLs Are Sorted

SQLite and MySQL put NULL first in ascending order; PostgreSQL and Oracle put it last. PostgreSQL, Oracle and SQLite 3.30+ support NULLS FIRST / NULLS LAST to control this.

Frequently asked questions

What is the default sort order in SQL ORDER BY?

Ascending (ASC). You only need to write DESC when you want descending order.

Can I ORDER BY a column that is not in SELECT?

Yes, in most cases. The exception is SELECT DISTINCT, where the sort column usually has to be part of the select list.

Can I order by column number?

Yes, ORDER BY 2 sorts by the second selected column. It works but is fragile, because the meaning changes if you reorder the select list.