Order by clause is used with Select statement for arranging retrieved data in sorted order. The Order byclause by default sort data in ascending order. To sort data in descending order DESC keyword is used withOrder by clause.
Syntax of Order By
SELECT column-list|* from table-name order by asc|desc;
Example using Order by
Consider the following Emp table,
| eid | name | age | salary |
|---|---|---|---|
| 401 | Anu | 22 | 9000 |
| 402 | Shane | 29 | 8000 |
| 403 | Rohan | 34 | 6000 |
| 404 | Scott | 44 | 10000 |
| 405 | Tiger | 35 | 8000 |
SELECT * from Emp order by salary;
The above query will return result in ascending order of the salary.
| eid | name | age | salary |
|---|---|---|---|
| 403 | Rohan | 34 | 6000 |
| 402 | Shane | 29 | 8000 |
| 405 | Tiger | 35 | 8000 |
| 401 | Anu | 22 | 9000 |
| 404 | Scott | 44 | 10000 |
Example of Order by DESC
Consider the Emp table described above,
SELECT * from Emp order by salary DESC;
The above query will return result in descending order of the salary.
| eid | name | age | salary |
|---|---|---|---|
| 404 | Scott | 44 | 10000 |
| 401 | Anu | 22 | 9000 |
| 405 | Tiger | 35 | 8000 |
| 402 | Shane | 29 | 8000 |
| 403 | Rohan | 34 | 6000 |
The SQL ORDER BY clause is used to sort the data in ascending or descending order, based on one or more columns. Some databases sort the query results in an ascending order by default.
Syntax
The basic syntax of the ORDER BY clause is as follows −
SELECT column-list FROM table_name [WHERE condition] [ORDER BY column1, column2, .. columnN] [ASC | DESC];
You can use more than one column in the ORDER BY clause. Make sure whatever column you are using to sort that column should be in the column-list.
Example
Consider the CUSTOMERS table having the following records −
+----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | 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 | MP | 4500.00 | | 7 | Muffy | 24 | Indore | 10000.00 | +----+----------+-----+-----------+----------+
The following code block has an example, which would sort the result in an ascending order by the NAME and the SALARY −
SQL> SELECT * FROM CUSTOMERS ORDER BY NAME, SALARY;
This would produce the following result −
+----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | 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 | MP | 4500.00 | | 7 | Muffy | 24 | Indore | 10000.00 | | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | +----+----------+-----+-----------+----------+
The following code block has an example, which would sort the result in the descending order by NAME.
SQL> SELECT * FROM CUSTOMERS ORDER BY NAME DESC;
This would produce the following result −
+----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 7 | Muffy | 24 | Indore | 10000.00 | | 6 | Komal | 22 | MP | 4500.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | +----+----------+-----+-----------+----------+