The SQL ORDER BY syntax

The general syntax is:

1. SELECT column–names
2. FROM table–name
3. WHERE condition
4. ORDER BY column–names

 

---

 

SQL ORDER BY Examples

Problem: List all suppliers in alphabetical order

1. SELECT CompanyName, ContactName, City, Country
2. FROM Supplier
3. ORDER BY CompanyName

The default sort order is ascending, that is, low-high or a-z.
Results: 29 records

 

---

Problem: List all suppliers in reverse alphabetical order

1. SELECT CompanyName, ContactName, City, Country
2. FROM Supplier
3. ORDER BY CompanyName DESC

The keyword DESC denotes descending, i.e., reverse order.
Results: 29 records

 

---

Problem: List all customers ordered by country, then by city within each country
Ordering by one or more columns is possible.

1. SELECT FirstName, LastName, City, Country
2. FROM Customer
3. ORDER BY Country, City

Results: 91 records

 

---

Problem: List all suppliers in the USA, Japan, and Germany, ordered by city,
then by company name in reverse order

1. SELECT Id, CompanyName, City, Country
2. FROM Supplier
3. WHERE Country IN (‘USA’, ‘Japan’, ‘Germany’)
4. 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

1. SELECT Id, OrderDate, CustomerId, TotalAmount
2. FROM [Order]
3. 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.