SQL ORDER BY Clause

The SQL ORDER BY clause used to sort records in the result set in either ascending or descending order based on one or more columns.

Most of the databases sort the result set in ascending order by default.

SQL ORDER BY syntax

SELECT expressions 
FROM tables 
[WHERE conditions] 
[ORDER BY expression [ ASC | DESC ]];

Here,

  • expressions – expressions defined here the column(s) or calculation you want to retrieve. If you want to retrieve all the columns simply use * in the place of expressions.
  • tables – one or more than one table from where you want to retrieve data.
  • WHERE conditions – Optional. This is used to specify some conditions while selecting data. In case you are not using the WHERE clause, all the rows available will be selected.
  • ORDER BY – This argument is used to sort the result set. If you want to sort on more than one column, you need to provide them in comma-separated.
  • ASC – Optional. ASC sort the result set in ascending order. This is the default behavior if no modifier is mentioned.
  • DESC – Optional. DESC sorts result set in descending order by expression.

SQL ORDER BY example

Let’s consider an Employee table with the following data:

ID NAME GENDER AGE SALARY
1510 Avinash M 35 33000
1511 Ramjan M 31 56000
1512 Priyanka F 32 35000
1513 Priyam M 25 42000
1514 Jitu M 23 43000
1515 Raman M 26 49000
1516 Lata F 25 27000
1517 Prakash M 38 45000
1518 Nitu F 40 45000
1519 Pallab M 33 42000
1520 Pankaj M 39 55000

The following example sorts the record of the Employee table in ascending order by NAME and SALARY.

SELECT * FROM Employee
ORDER BY NAME, SALARY;

This would produce the following result.

ID NAME GENDER AGE SALARY
1510 Avinash M 35 33000
1514 Jitu M 23 43000
1516 Lata F 25 27000
1518 Nitu F 40 45000
1519 Pallab M 33 42000
1520 Pankaj M 39 55000
1513 Priyam M 25 42000
1512 Priyanka F 32 35000
1517 Prakash M 38 45000
1515 Raman M 26 49000
1511 Ramjan M 31 56000

Let’s check another example of sorting columns in descending order.

In the below example, the records of the Employee table will be sorted in descending order by NAME.

SELECT * FROM Employee
ORDER BY NAME DESC;

This would produce the following result:

ID NAME GENDER AGE SALARY
1511 Ramjan M 31 56000
1515 Raman M 26 49000
1517 Prakash M 38 45000
1512 Priyanka F 32 35000
1513 Priyam M 25 42000
1520 Pankaj M 39 55000
1519 Pallab M 33 42000
1518 Nitu F 40 45000
1516 Lata F 25 27000
1514 Jitu M 23 43000
1510 Avinash M 35 33000

Topics of the SQL ORDER BY Clause

1. SQL ORDER BY ASC – Sort the records in ascending order.

2. SQL ORDER BY DESC – Sort the records in descending order.

3. SQL ORDER BY RANDOM – Select random data from the table

4. SQL ORDER BY LIMIT – Limits the records in the result set.

5. SQL ORDER BY Multiple Columns – Sort data by multiple columns 

Summary: In this tutorial, you have learned how to use SQL ORDER BY clause to sort the result set in ascending and descending order.

Leave a Comment