SQL ORDER BY

SQL Delete

SQL Join

By default, SELECT returns rows in no particular order.

The ORDER BY clause returns the rows in a given sort order.

Rows can be returned in ascending or descending sort order.

Example

Syntax

#

ORDER BY syntax.

SELECT column-names
  FROM table-name
 WHERE condition
 ORDER BY column-names [ASC, DESC]

column-names -- one or more columns names on which to sort on.

ASC -- ascending sort order: low to high, a to z. This is the default.

DESC -- descending sort order: high to low, z to a.

More Examples

ORDER BY, ascending order

SUPPLIER
Id
CompanyName
ContactName
City
Country
Phone
Fax

Problem: List all suppliers in alphabetical order.

SELECT Id, 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

ORDER BY, descending order

SUPPLIER
Id
CompanyName
ContactName
City
Country
Phone
Fax

Problem: List all suppliers in reverse alphabetical order.

SELECT Id, 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

ORDER BY, multiple columns

CUSTOMER
Id
FirstName
LastName
City
Country
Phone

Problem: List all customers ordered by country, then by city within each country.

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

As you can see, sorting by multiple columns is entirely possible.

ORDER BY, ascending and descending

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.

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, each one in ascending or descending sort 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 BY, with dates

ORDER
Id
OrderDate
OrderNumber
CustomerId
TotalAmount

Problem: List all orders, sorted by amount (largest first), within each year.

SELECT Id, OrderDate, CustomerId, TotalAmount
  FROM [Order]
 ORDER BY YEAR(OrderDate) ASC, TotalAmount DESC

DESC denotes descending sort order.
The table name [Order] is bracketed because it is a keyword 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

The results show the following year breakpoints: 2012 - 2013 and 2013 - 2014. Each year starts with the highest TotalAmounts. Other data types, such as numbers, strings, dates, and bits can also be sorted.

YEAR is a built-in function that returns the year part for a given date.