Sorting Data

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

Note

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