SQL ORDER BY

SQL ORDER BY Explained

A SELECT statement returns records in no particular order.

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

SQL ORDER BY allows sorting by one or more columns.

Records can be returned in ascending or descending order.

Example

#

Problem: List all customers sorted by country.

SELECT * 
  FROM Customer
 ORDER BY Country

Syntax

ORDER BY syntax.

SELECT column-names
  FROM table-name
 WHERE condition
 ORDER BY column-names

More Examples

SQL ORDER BY

SUPPLIER
Id
CompanyName
ContactName
City
Country
Phone
Fax
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

SQL ORDER BY DESC

SUPPLIER
Id
CompanyName
ContactName
City
Country
Phone
Fax
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.

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

SQL ORDER BY Multi-column

CUSTOMER
Id
FirstName
LastName
City
Country
Phone
Problem: List all customers ordered by country, then by city within each country.
Note: Ordering by multiple 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

SQL ORDER BY ASC, DESC

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.
ASC denotes ascending sort order which is the default.

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

SQL ORDER BY with Date

ORDER
Id
OrderDate
OrderNumber
CustomerId
TotalAmount
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 denotes descending sort order.
Also, 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 for a given date.


You may also like



Guides