Sorting Data

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.

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

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

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 DESC is ASC.
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;

The prod_price column is sorted in descending order, but the prod_name column (within each price) is still sorted in standard ascending order.