SQL ORDER BY

ORDER BY command is used to sort results either in ascending or descending order. It is often used in a SELECT statement.

In most of the database systems like Oracle, it sorts query results in ascending order by default.

To sort the result set in descending order use the DESC keyword.

ORDER BY Syntax

SELECT column-list
FROM table_name [WHERE condition]
[ORDER BY column1 [, column2, .. columnN] [DESC]];
In above syntax the fields in square brackets are optional.

Example

Consider the following demo employee table.
id name dept age salary
1 Shahrukh Arts 24 25000
2 Salman Commerce 28 35000
3 Aamir Science 28 35000
4 Tiger Sports 22 20000
5 John Wrestling 25 30000

The following SQL statement selects all the columns from employee table sorted by the "salary" column.
SELECT name, salary FROM employee ORDER BY salary;
The output would be like:
name           salary
----------  ----------
Tiger           20000
Shahrukh        25000
John            30000
Salman          35000
Aamir           35000
The records first get sorted by salary in ascending order and then get displayed.

ASC

Although the records in table get sorted in ascending order by default, you can explicitly specify it using ASC keyword.

After the change above sql query would simply look like
SELECT name, salary FROM employee ORDER BY salary ASC;
NOTE: You can specify more than one column in the ORDER BY clause. The columns specified in ORDER BY clause should be one of the columns specified in the SELECT column list.

You can also represent the columns in the ORDER BY clause by specifying the position of a column in the SELECT list, instead of writing the column name.

The above query can also be written as given below,
SELECT name, salary FROM employee ORDER BY 2 ASC;

DESC

By default, SQL ORDER BY sorts data in ascending order. If you want to sort the data in descending order, you must explicitly specify it using DESC keyword.

Following sql statement sorts the employee records in descending order based on the "salary" column.
SELECT name, salary
FROM employee
ORDER BY salary DESC;
Now as mentioned earlier more than one column names can be used in ORDER BY.

The following sql query sorts the employee records in ascending order by column "name" and descending order by column "salary".
SELECT name, salary
FROM employee
ORDER BY name, salary DESC;
If you want to sort both name and salary in descending order, the query would be as given below.

SELECT name, salary
FROM employee
ORDER BY name DESC, salary DESC;

How to use expressions in the ORDER BY Clause?

Example: If you want to display employee name, current salary, and a 20% increase in the salary for only those employees for whom the percentage increase in salary is greater than 30000 and in descending order of the increased price, the SELECT statement can be written as shown below

SELECT name, salary, salary*0.2 AS new_salary
FROM employee
WHERE salary*0.2 > 30000
ORDER BY new_salary DESC;
The output is:
 name         salary     new_salary
----------  ----------  -------------
 Salman       35000       37000
 Aamir        35000       37000
 John         30000       36000
Prev - SQL NOT IN Operator Next - SQL Group Functions

Comments