SQL ORDER BY | Sort Rows According to a Column

The ORDER BY statement in SQL sorts the rows in ascending or descending order according to one or more columns. By default, it sorts the rows in ascending order. However, we can also sort the rows in descending order by using the DESC keyword. In this article, we will discuss the SQL ORDER BY statement. Also, we will discuss a few examples of using it.

Syntax

Sorting According to One Column

SELECT * FROM table ORDER BY column1 ASC | DESC;

The above query sorts the rows in Ascending or Descending order according to the ASC or DESC respectively. Also, if you don’t specify the sort order, it sorts by ascending order.

Sorting According to Multiple Columns

Similarly, you can also sort the results according to multiple columns by separating them with a comma.

SELECT * FROM table ORDER BY column1 ASC | DESC, column2 ASC | DESC;

Also, we can define the sort order in ORDER BY in SQL for different columns separately while using multiple columns. If no sorting order is specified, the default order is in ascending order.

Demo Table

For instance, consider the demo table to refer for the all the queries in this article.

mysql> SELECT * FROM employees;
+----+--------+--------+------------+-------+
| id | name   | salary | experience | team  |
+----+--------+--------+------------+-------+
|  1 | David  |  20000 |          5 | Alpha |
|  2 | Monica |  25000 |          6 | Beta  |
|  3 | John   |  15000 |          3 | Beta  |
|  4 | Mary   |  18000 |          4 | Gamma |
|  5 | Ross   |  30000 |          8 | Alpha |
| 12 | Joe    |  20000 |         10 | Beta  |
| 13 | Steve  |  15000 |          4 | Alpha |
+----+--------+--------+------------+-------+

Examples

Consider the following examples of using the SQL ORDER BY Statement.

Example 1: Ordering By One Column

For instance, consider ordering the rows in ascending order of their salary.

mysql> SELECT * FROM employees ORDER BY salary;
+----+--------+--------+------------+-------+
| id | name   | salary | experience | team  |
+----+--------+--------+------------+-------+
|  3 | John   |  15000 |          3 | Beta  |
| 13 | Steve  |  15000 |          4 | Alpha |
|  4 | Mary   |  18000 |          4 | Gamma |
|  1 | David  |  20000 |          5 | Alpha |
| 12 | Joe    |  20000 |         10 | Beta  |
|  2 | Monica |  25000 |          6 | Beta  |
|  5 | Ross   |  30000 |          8 | Alpha |
+----+--------+--------+------------+-------+

In the above example, the query sorts the results in ascending order of their salary. The default sorting order in ORDER BY in SQL is Ascending.

Example 2: Sorting in Descending

Similarly, we can sort the rows in descending order by passing the DESC keyword after the ORDER BY statement.

mysql> SELECT * FROM employees ORDER BY salary DESC;
+----+--------+--------+------------+-------+
| id | name   | salary | experience | team  |
+----+--------+--------+------------+-------+
|  5 | Ross   |  30000 |          8 | Alpha |
|  2 | Monica |  25000 |          6 | Beta  |
|  1 | David  |  20000 |          5 | Alpha |
| 12 | Joe    |  20000 |         10 | Beta  |
|  4 | Mary   |  18000 |          4 | Gamma |
|  3 | John   |  15000 |          3 | Beta  |
| 13 | Steve  |  15000 |          4 | Alpha |
+----+--------+--------+------------+-------+

Example 3: Sorting By Multiple Columns

When we pass multiple columns for sorting, the query first sorts according to the first column. If there are any same values in the first column, then it considers the second column for sorting.

For example, let’s sort the rows by ascending order of their salary and descending order of their experience using ORDER BY in SQL.

mysql> SELECT * FROM employees ORDER BY salary ASC, experience DESC;
+----+--------+--------+------------+-------+
| id | name   | salary | experience | team  |
+----+--------+--------+------------+-------+
| 13 | Steve  |  15000 |          4 | Alpha |
|  3 | John   |  15000 |          3 | Beta  |
|  4 | Mary   |  18000 |          4 | Gamma |
| 12 | Joe    |  20000 |         10 | Beta  |
|  1 | David  |  20000 |          5 | Alpha |
|  2 | Monica |  25000 |          6 | Beta  |
|  5 | Ross   |  30000 |          8 | Alpha |
+----+--------+--------+------------+-------+

In the above example, Steve and John have the same salary (15000), therefore the query sorts it in descending order of their experience. Similarly, Joe and David have the same salary, and are sorted according to their experience in descending order.

Conclusion

In this article, we discussed the SQL ORDER BY statement. You can read more about on Wikipedia. Additionally, you can also read more articles on SQL on Concatly.

Spread the Knowledge

Leave a Reply

Your email address will not be published. Required fields are marked *