We can use the ORDER BY keywords to select a column to use for ordering retuned data from the database.
Order product by product name
Lets retrieve the products and order them by product name. By default the ORDER BY clause orders the rows in ascending order.
SELECT prod_name FROM products ORDER BY prod_name
When ordering rows, we can use a different column in the order by clause even if the column is not specified in the list of columns to be retrieved from the database.
Sorting by Multiple Columns
We can specify multiple columns in the ORDER BY clause. The column names should be separated by commas.
Sort by product price and product name
SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price, prod_name
The above sql statement will first sort by product price and if they are matching products with the same name, then it will sort first with the price and then with the product name.
Sorting by Column Position
Instead of using the column name in the ORDER BY clause, we can specify instead the column position. The first column is position 1.
SELECT prod_name FROM products ORDER BY 1
The above sql statement will sort by rows by the product name since its on column 1.
The statement will return the same result as the previous statement sorting rows by multiple columns. We are using the column positions for prod_price which is column 1 and prod_name which is column 3.
SELECT prod_id, prod_price, prod_name FROM products ORDER BY 2, 3
Specifying Sort Direction
The default sort direction is ascending, A to Z for characters or smallest to highest for numbers. We can change the sort direction by using the keywords
ASC for ascending
DESC for descending together with the ORDER BY clause.
Retrieve products by highest price first
SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price DESC
When specifying multiple columns in the ORDER BY clause, the DESC or ASC keywords only applies to the column name preceding it.
The statement below will order the rows by the highest price first and then by product name in ascending order.
SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price DESC, prod_name