sql-order-by-clause

SQL - ORDER BY Clause

The ORDER BY clause is used to sort the result-set of a SELECT statement in ascending or descending order based on one or more columns. Without an ORDER BY clause, the order of rows returned from a query is not guaranteed.

Syntax

The basic syntax of the ORDER BY clause is as follows:

SELECT column_list
FROM table_name
[WHERE condition]
ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ...;

• ASC (Ascending): Sorts the results from lowest to highest. This is the default sorting order, so it can be omitted.
• DESC (Descending): Sorts the results from highest to lowest.

You can sort by multiple columns. The result is sorted by the first column, and then for any rows with the same value in the first column, they are sorted by the second column, and so on.

Example

Using our Customers table:

+----+----------+-----+-------------+----------+
| ID | Name     | Age | City        | Salary   |
+----+----------+-----+-------------+----------+
|  1 | Ramesh   |  32 | Ahmedabad   |  2000.00 |
|  2 | Khilan   |  25 | Delhi       |  1500.00 |
|  3 | Kaushik  |  23 | Kota        |  2000.00 |
|  4 | Chaitali |  25 | Mumbai      |  6500.00 |
|  5 | Hardik   |  27 | Bhopal      |  8500.00 |
|  6 | Komal    |  22 | Hyderabad   |  4500.00 |
|  7 | Muffy    |  24 | Indore      | 10000.00 |
+----+----------+-----+-------------+----------+

The following query sorts all customers alphabetically by Name.

SELECT * FROM Customers
ORDER BY Name ASC; -- ASC is optional here

This would produce:

+----+----------+-----+-------------+----------+
| ID | Name     | Age | City        | Salary   |
+----+----------+-----+-------------+----------+
|  4 | Chaitali |  25 | Mumbai      |  6500.00 |
|  5 | Hardik   |  27 | Bhopal      |  8500.00 |
|  3 | Kaushik  |  23 | Kota        |  2000.00 |
|  2 | Khilan   |  25 | Delhi       |  1500.00 |
|  6 | Komal    |  22 | Hyderabad   |  4500.00 |
|  7 | Muffy    |  24 | Indore      | 10000.00 |
|  1 | Ramesh   |  32 | Ahmedabad   |  2000.00 |
+----+----------+-----+-------------+----------+

The following query sorts customers by Age in descending order. For customers with the same age, it sorts them by Name in ascending order.

SELECT * FROM Customers
ORDER BY Age DESC, Name ASC;

This would produce:

+----+----------+-----+-------------+----------+
| ID | Name     | Age | City        | Salary   |
+----+----------+-----+-------------+----------+
|  1 | Ramesh   |  32 | Ahmedabad   |  2000.00 |
|  5 | Hardik   |  27 | Bhopal      |  8500.00 |
|  4 | Chaitali |  25 | Mumbai      |  6500.00 |
|  2 | Khilan   |  25 | Delhi       |  1500.00 |
|  7 | Muffy    |  24 | Indore      | 10000.00 |
|  3 | Kaushik  |  23 | Kota        |  2000.00 |
|  6 | Komal    |  22 | Hyderabad   |  4500.00 |
+----+----------+-----+-------------+----------+

Notice how ‘Chaitali’ and ‘Khilan’, both age 25, are sorted alphabetically by name.

Sorting by Column Position

While not generally recommended for readability, you can also sort by the position of the column in the SELECT list. For example, ORDER BY 2 would sort by the second column in the SELECT list.

-- Sorts by the 'Name' column, which is the 2nd in the list
SELECT ID, Name, Salary FROM Customers
ORDER BY 2;