In data analysis it is common to sort the data by various criteria in order to answer specific questions. In SQL, sorting is done with the ORDER BY clause, which sorts a query result in ascending or descending order based on the columns or aggregate functions specified. The table below will be used in examples of the various ways you can use ORDER BY that follow.
| id | first_name | last_name |
|---|---|---|
| 1 | Nanni | Saphin |
| 2 | Odell | McGlaud |
| 3 | Bea | McTurlough |
| 4 | Naomi | Simonett |
| 5 | Garek | Dalloway |
| 6 | Fonz | Janikowski |
| 7 | Sinclare | Smeath |
| 8 | Eryn | Chable |
| 9 | Noel | Lapidus |
| 10 | Walsh | Reaman |
| ... | ... | ... |
| 45 | Pam | Maharry |
| 46 | Sula | Ferrant |
| 47 | Margareta | Scarfe |
By default, using the ORDER BY will sort the query in ascending order by the values provided.
SELECT id
, first_name
, last_name
FROM employee
ORDER BY first_name;Result Set:
| id | first_name | last_name |
|---|---|---|
| 29 | Alfy | Pearcehouse |
| 26 | Annice | Pygott |
| 38 | Ariella | Binder |
| 3 | Bea | McTurlough |
As you can see in this example, using ORDER BY with the first_name sorted the result set in alphabetical order starting with the letter A. Note that you can also specify the keyword ASC, to sort in ascending order.
If you want to sort in descending order, then you must use the keyword DESC after the value.
SELECT id
, first_name
, last_name
FROM employee
ORDER BY first_name DESC;Result Set:
| id | first_name | last_name |
|---|---|---|
| 10 | Walsh | Reaman |
| 24 | Viva | Garrals |
| 16 | Viole | Chidzoy |
| 36 | Tori | Lofthouse |
Using the example SQL query above, adding DESC sorted our result set in the reverse order starting with W which is the lowest letter in the alphabet for our data table.
ORDER BY also accepts the index value of the column order presented in the SELECT clause.
SELECT id
, first_name
, last_name
FROM employee
ORDER BY 1 DESC;Result Set:
| id | first_name | last_name |
|---|---|---|
| 47 | Margareta | Scarfe |
| 46 | Sula | Ferrant |
| 45 | Pam | Maharry |
| 44 | Bevin | Lambertz |
As you can see from the result set, the “1” translates into id, which is the first column in our SELECT clause which is sorted in descending order.
Columns do not have to be present in the SELECT clause in order to be used in the ORDER BY clause.
SELECT id
, last_name
FROM employee
ORDER BY first_name;Result Set:
| id | last_name |
|---|---|
| 29 | Pearcehouse |
| 26 | Pygott |
| 38 | Binder |
| 3 | McTurlough |
In this example, we left out the first_name column from our SELECT clause, but use it to sort the result set in alphabetical order by the employees first name. If you look back to our SQL query first example, you will see that result sets are identical with Alfy Pearcehouse as the first record in both result sets.
Here is a different data table that will be used for the examples that follow.
| id | product_category | product_name | product_stock | product_price |
|---|---|---|---|---|
| 1 | "Category 1" | "Product 1" | "TRUE" | 3540.032 |
| 2 | "Category 2" | "Product 2" | "TRUE" | 3374.566 |
| 3 | "Category 4" | "Product 3" | "TRUE" | 1609.558 |
| 4 | "Category 4" | "Product 4" | "FALSE" | 3098.181 |
| 5 | "Category 2" | "Product 5" | "TRUE" | 3800.55 |
| 6 | "Category 4" | "Product 6" | "TRUE" | 3618.446 |
| 7 | "Category 3" | "Product 7" | "FALSE" | 2649.385 |
| 8 | "Category 2" | "Product 8" | "TRUE" | 3342.788 |
| 9 | "Category 1" | "Product 9" | "TRUE" | 2225.721 |
| 10 | "Category 2" | "Product 10" | "FALSE" | 3338.701 |
| 11 | "Category 3" | "Product 11" | "FALSE" | 3734.841 |
| 12 | "Category 3" | "Product 12" | "TRUE" | 1794.479 |
| 13 | "Category 3" | "Product 13" | "FALSE" | 2228.005 |
| 14 | "Category 1" | "Product 14" | "FALSE" | 2539.28 |
| 15 | "Category 3" | "Product 15" | "TRUE" | 3983.513 |
| 16 | "Category 4" | "Product 16" | "FALSE" | 2211.937 |
| 17 | "Category 4" | "Product 17" | "FALSE" | 1669.626 |
| 18 | "Category 2" | "Product 18" | "TRUE" | 3793.368 |
| 19 | "Category 2" | "Product 19" | "FALSE" | 3131.272 |
| 20 | "Category 2" | "Product 20" | "FALSE" | 1541.632 |
Let us say that we want to see the product categories sorted by the average product price. We can achieve this by using the AVG() aggregation function in our ORDER BY.
SELECT product_category
, AVG(product_price) AS avg_product_price
FROM products
GROUP BY 1
ORDER BY AVG(product_price) DESC;Result Set:
| product_category | avg_product_price |
|---|---|
| Category 2 | 3188.9824285714285 |
| Category 3 | 2878.0446 |
| Category 1 | 2768.344333333334 |
| Category 4 | 2441.5496 |
As you can see from the result set we have averaged the product price by product_category and ordered it in descending order.
Aggregate functions aren’t the only functions that can be used in ORDER BY. You can also use other functions to manipulate and sort. We can do something like concatenate the product_stock by its first letter with the number associated with the product name and sort by this new column called stocked_name. Note the combination of multiple columns in the example below. ORDER BY accepts sorting by multiple values as long as they are separated by a comma.
SELECT product_category
, product_name
, product_stock
, LEFT(product_stock,1) || ' ' || RIGHT(product_name,2) AS stocked_name
FROM products
GROUP BY 1,2,3
ORDER BY product_category, LEFT(product_stock,1) || ' ' || RIGHT(product_name,2);Result Set:
| product_category | product_name | product_stock | stocked_name |
|---|---|---|---|
| Category 1 | Product 14 | FALSE | F 14 |
| Category 1 | Product 1 | TRUE | T 1 |
| Category 1 | Product 9 | TRUE | T 9 |
| Category 2 | Product 10 | FALSE | F 10 |
| Category 2 | Product 19 | FALSE | F 19 |
| ... | ... | ... | ... |
| Category 4 | Product 3 | TRUE | T 3 |
| Category 4 | Product 6 | TRUE | T 6 |