SQL - ORDER BY Clause

The ORDER BY clause can be used in the SELECT query to sort the result in ascending or descending order of one or more columns.

Syntax:

SELECT column1, column2,...columnN FROM table_name [WHERE] [GROUP BY] [HAVING] [ORDER BY column(s) [ASC|DESC]]

For the demo purpose, we will use the following Employee in all examples.

The following query will fetch all the records from the Employee table and sorts the result in ascending order of the FirstName values.

SELECT * FROM Employee
ORDER BY FirstName;

The following query will return rows in the descending order of the FirstName value.

SELECT EmpId, FirstName, LastName FROM Employee
ORDER BY FirstName DESC;

Sort by Multiple Columns

The ORDER BY clause can include multiple columns in different sorting order (ascending or descending). When you include multiple columns with the ORDER BY clause, it will sort the records based on the first column, and if any two or more records have the same value in the first ORDER BY column, it will sort them by the second ORDER BY column.

To understand this, first, sort the result by DeptId column, as shown below.

SELECT * FROM Employee
ORDER BY DeptId;

The above query would display the following result.

Now, include the FirstName column in the ORDER BY clause.

SELECT * FROM Employee
ORDER BY DeptId, FirstName;

The above query will first sort the result by DeptId, and then the rows having the same DeptId will be sorted by the FirstName. Remember, we have not included ASC or DESC, So it will sort the result in ascending order by default, as shown below.

Sort Group of Records

The following query sorts the group of records.

SELECT dept.Name as 'Department', count(emp.empid) as 'No of Employees' FROM Employee emp, Department dept WHERE emp.deptid = dept.DeptId GROUP by dept.Name ORDER BY dept.Name DESC