SQL ORDER BY Clause
How do I get records in a certain sort order?
SELECT (without ORDER BY) returns records in no particular order.
To ensure a specific sort 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 SQL ORDER BY syntax
The general ORDER BY syntax is
SELECT column-names FROM table-name WHERE condition ORDER BY column-names
SUPPLIER |
---|
Id |
CompanyName |
ContactName |
City |
Country |
Phone |
Fax |
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.
Result: 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 |
![]() |
SUPPLIER |
---|
Id |
CompanyName |
ContactName |
City |
Country |
Phone |
Fax |
Problem: Get a list of all suppliers in
reverse alphabetical order
reverse alphabetical order
SELECT CompanyName, ContactName, City, Country FROM Supplier ORDER BY CompanyName DESC
The keyword DESC denotes descending, i.e., reverse order.
Result: 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 |
![]() |
CUSTOMER |
---|
Id |
FirstName |
LastName |
City |
Country |
Phone |
Problem: List all customers ordered by country,
then by city within each country
then by city within each country
Note: Ordering by one or more columns is entirely possible.
SELECT FirstName, LastName, City, Country FROM Customer ORDER BY Country, City
Result: 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 |
![]() |
SUPPLIER |
---|
Id |
CompanyName |
ContactName |
City |
Country |
Phone |
Fax |
Problem: List all suppliers in the USA, Japan, and Germany,
ordered by city, then by company name in reverse order.
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 order.
ASC denotes ascending, but is optional as it is the default order.
Result: 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 |
ORDER |
---|
Id |
OrderDate |
OrderNumber |
CustomerId |
TotalAmount |
Problem: Show all orders, sorted by total amount,
the largest first, within each year
the largest first, within each year
SELECT Id, OrderDate, CustomerId, TotalAmount FROM [Order] ORDER BY YEAR(OrderDate) ASC, TotalAmount DESC
Note: DESC means descending.
Also, [Order] must be bracketed because it is a keywork in SQL.
Also, [Order] must be bracketed because it is a keywork in SQL.
Result: 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, strings, dates, and bits can also be sorted.
YEAR is a built-in function that returns the year from a date.
This shows that other data types, such as numbers, strings, dates, and bits can also be sorted.
YEAR is a built-in function that returns the year from a date.