SQL ORDER BY

Another important clause used along with SQL SELECT is the ORDER BY clause. ORDER BY defines in what order do we get the data we have requested. Here is an example of using SQL ORDER BY:

 SELECT * FROM Cars ORDER BY Year;

The SQL statement above will select all columns from the Cars table, and will order them by the Year column, returning a result like this:

CarMake	Model	Year	Color
Honda	Accord EX	2002	Black
Toyota	Camry XLE	2005	Gray
BMW	3 Series Coupe	2008	Red
Lexus	ES 350	2008	Grayr

You can order by more than one column, by simply typing a comma-separated list of column names after the ORDER By clause. For example if you want to order by both Year and Color, you can use the following:

 SELECT * FROM Cars ORDER BY Year, Color; 

The result will be this:

CarMake	Model	Year	Color
Honda	Accord EX	2002	Black
Toyota	Camry XLE	2005	Gray
Lexus	ES 350	2008	Gray
BMW	3 Series Coupe	2008	Red

The Gray Lexus goes on top of the red BMW even though they are the same year, because the Color column is ordered alphabetically and Gray goes first.

So far all ORDER BY statements above ordered the data alphabetically, but what if we want the data ordered backwards? To answer this question we must introduce ASC and DESC keywords. The ASC and DESC keywords determine the order in which the results are displayed. If you don't specify ASC or DESC after the ORDER BY clause, the SQL interpreter defaults the order to ascending, which means that the following 2 SQL statements yield the same result:

 SELECT * FROM Cars ORDER BY CarMake; 

 SELECT * FROM Cars ORDER BY CarMake ASC; 

And here is the result:

CarMake	Model	Year	Color
BMW	3 Series Coupe	2008	Red
Honda	Accord EX	2002	Black
Lexus	ES 350	2008	Gray
Toyota	Camry XLE	2005	Gray

If you specify DESC instead of ASC in the SQL expression above, you will get this result:

CarMake	Model	Year	Color
Toyota	Camry XLE	2005	Gray
Lexus	ES 350	2008	Gray
Honda	Accord EX	2002	Black
BMW	3 Series Coupe	2008	Red

You can specify ASC or DESC, after each column following ORDER BY. For example:

 SELECT * FROM Cars ORDER BY Year ASC, Color DESC; 

The result will be:

CarMake	Model	Year	Color
Honda	Accord EX	2002	Black
Toyota	Camry XLE	2005	Gray
BMW	3 Series Coupe	2008	Red
Lexus	ES 350	2008	Gray

First the result set is ordered by Year ascending, and then for the rows having one and the same year as the BMW and Lexus ones, the Color column is ordered descending, that's why the Red BMW comes on top of the Gray Lexus.