SQL Server - ORDER BY Clause

In SQL Server, the ORDER BY clause is 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]]

ORDER BY Characteristics:

• The ORDER BY clause is used to get the sorted records on one or more columns in ascending or descending order.
• The ORDER BY clause must come after the WHERE, GROUP BY, and HAVING clause if present in the query.
• Use ASC or DESC to specify the sorting order after the column name. Use ASC to sort the records in ascending order or use DESC for descending order. By default, the ORDER BY clause sort the records in ascending order if the order is not specified.

For the demo purpose, we will use the following Employee and Department 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 above query will return the following result in SQL Server.

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

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

The above query will return the following result in SQL Server.

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.DeptName
ORDER BY dept.DeptName DESC