This post is heavily based on Sams Teach Yourself SQL in 10 Minutes (Fourth Edition). Please consider to buy the book if you find this post useful.
To explicitly sort data retrieved using a
SELECT statement, the
ORDER BY clause is used.
ORDER BY takes the name of one or more columns by which to sort the output.
SQL statements are made up of clauses, some required and some optional. A clause usually consists of a keyword and supplied data. An example of this is the SELECT statement’s FROM clause.
SELECT prod_name FROM Products ORDER BY prod_name;
The following shows before and after sorting:
What is interesting here is that
8 inch teddy bear is located below the 12 and the 18 inches’.
ORDER BY Clause
When specifying an
ORDER BY clause, be sure that it is the last clause in your
SELECT statement. If it is not the last clause, an error will be generated.
Sorting by Nonselected Columns
It is perfectly legal to sort data by a column that is not retrieved such as the following:
SELECT prod_name FROM Products ORDER BY vend_id;
Sorting by Multiple Columns
To sort by multiple columns, simply specify the column names separated by commas.
The following code retrieves three columns and sorts the results by two of them -first by price and then by name.
SELECT prod_id, prod_price, prod_name FROM Products ORDER BY prod_price, prod_name;
Note that the sort sequence is exactly as specified. For example, the products are sorted by the
prod_name column only when multiple rows have the same
prod_price value. If all the values in the
prod_price column had been unique, no data would have been sorted by
Sorting by Column Position
Instead of naming out the columns to be sorted, we can put it the column number. The follwing goves the same output as the above:
SELECT prod_id, prod_price, prod_name FROM Products ORDER BY 2, 3;
The main limitation of this technique is you cannot sort by columns that are not in the
SELECT list However, you can mix and match actual column names and relative column positions in a single statement if needed.
Specifying Sort Direction
Bt default, the columns selected are sorted in ascending order. To sort by descending order, the keyword
DESC must be specified. The opposite of
The following example sorts the products by price in descending order (most expensive first):
SELECT prod_id, prod_price, prod_name FROM Products ORDER BY prod_price DESC;
What if you were to sort by multiple columns? The
DESC keyword only applies to the column name that directly precedes it. For example:
SELECT prod_id, prod_price, prod_name FROM Products ORDER BY prod_price DESC, prod_name;
prod_price column is sorted in descending order, but the
prod_name column (within each price) is still sorted in standard ascending order.
If you want to sort descending on multiple columns, be sure each column has its own
Depending on your DBMS, A might be same as a, and a come before B or after Z. If you do need an alternate sort order, you may not be able to accomplish this with a simple
ORDER BY clause.