SQL ORDER BY Clause

  • SELECT returns records in no particular order.
  • To ensure a specific order use the ORDER BY clause.
  • ORDER BY allows sorting by one or more columns.
  • Records can be returned in ascending or descending order.
The definitive guide
for data professionals


Order today!
 See 2 min video



The SQL ORDER BY syntax


The general syntax is:
SELECT column-names
  FROM table-name
 WHERE condition
 ORDER BY column-names




SQL ORDER BY Examples



Problem: List all suppliers in alphabetical order
SELECT CompanyName, ContactName, City, Country
  FROM Supplier
 ORDER BY CompanyName

The default sort order is ascending, that is, low-high or a-z.

Results: 29 records

Id CompanyName ContactName City Country
18 Aux joyeux ecclésiastiques Guylène Nodier Paris France
16 Bigfoot Breweries Cheryl Saylor Bend USA
5 Cooperativa de Quesos 'Las Cabras' Antonio del Valle Saavedra Oviedo Spain
27 Escargots Nouveaux Marie Delamare Montceau France
1 Exotic Liquids Charlotte Cooper London UK







Problem: List all suppliers in reverse alphabetical order
SELECT CompanyName, ContactName, City, Country
  FROM Supplier
 ORDER BY CompanyName DESC

The keyword DESC denotes descending, i.e., reverse order.

Results: 29 records

Id CompanyName ContactName City Country
22 Zaanse Snoepfabriek Dirk Luchte Zaandam Netherlands
4 Tokyo Traders Yoshi Nagase Tokyo Japan
17 Svensk Sjöföda AB Michael Björn Stockholm Sweden
8 Specialty Biscuits, Ltd. Peter Wilson Manchester UK
10 Refrescos Americanas LTDA Carlos Diaz Sao Paulo Brazil






Problem: List all customers ordered by country, then by city within each country
Ordering by one or more columns is possible.
SELECT FirstName, LastName, City, Country
  FROM Customer
 ORDER BY Country, City


Results: 91 records

Id FirstName LastName City Country
12 Patricio Simpson Buenos Aires Argentina
54 Yvonne Moncada Buenos Aires Argentina
64 Sergio Gutiérrez Buenos Aires Argentina
20 Roland Mendel Graz Austria
59 Georg Pipps Salzburg Austria
50 Catherine Dewey Bruxelles Belgium
76 Pascale Cartrain Charleroi Belgium







Problem: List all suppliers in the USA, Japan, and Germany, ordered by city,
then by company name in reverse order
SELECT Id, CompanyName, City, Country
  FROM Supplier
 WHERE Country IN ('USA', 'Japan', 'Germany')
 ORDER BY Country ASC, CompanyName DESC


This shows that you can order by more than one column.
ASC denotes ascending, but is optional as it is the default sort order.


Results: 9 records

Id CompanyName City Country
12 Plutzer Lebensmittelgroßmärkte AG Frankfurt Germany
13 Nord-Ost-Fisch Handelsgesellschaft mbH Cuxhaven Germany
11 Heli Süßwaren GmbH & Co. KG Berlin Germany
4 Tokyo Traders Tokyo Japan
6 Mayumi's Osaka Japan
2 New Orleans Cajun Delights New Orleans USA
19 New England Seafood Cannery Boston USA
3 Grandma Kelly's Homestead Ann Arbor USA
16 Bigfoot Breweries Bend USA







Problem: Show all orders, sorted by total amount, the largest first, within each year
SELECT Id, OrderDate, CustomerId, TotalAmount
  FROM [Order]
 ORDER BY YEAR(OrderDate) ASC, TotalAmount DESC

Note: DESC means descending, but is optional as it is the default sort order.
[Order] must be bracketed because it also is a keywork in SQL.
Results: 830 records.

Id OrderDate CustomerId TotalAmount
125 2012-12-04 00:00:00.000 62 12281.20
106 2012-11-13 00:00:00.000 59 10741.60
113 2012-11-22 00:00:00.000 7 7390.20
144 2012-12-23 00:00:00.000 17 86.40
24 2012-08-01 00:00:00.000 75 48.00
177 2013-01-23 00:00:00.000 51 11493.20
170 2013-01-16 00:00:00.000 73 11283.20
560 2013-12-31 00:00:00.000 27 18.40
535 2013-12-17 00:00:00.000 12 12.50
618 2014-02-02 00:00:00.000 63 17250.00
783 2014-04-17 00:00:00.000 71 16321.90

Notice the year breakpoints: 2012 - 2013 and 2013 - 2014. Each year starts with the highest TotalAmounts.
This shows that other data types, such as numbers, dates, and bits can also be sorted.
Note: YEAR is a built-in function which returns the year from a date.