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
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
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.
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.
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
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.
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.


Stay Inspired!
Join other developers and designers who have already signed up for our mailing list.
Terms     Privacy     Licensing       EULA       Sitemap      
© Data & Object Factory, LLC.
Made with    in Austin, Texas