Order by in MS SQL Server

Order by is a clause in SQL. It is used to sort the result set of a query in either ascending or descending order. It can sort using one or more columns. In this article, we will discuss the Order by clause in MS SQL Server.

Syntax

The syntax for using the Order by clause in MS SQL Server is as follows −

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

Explanation of Syntax

• SELECT column1, column2, ... : Specifies the columns that we want to retrieve from the table.

• FROM table_name: Specifies the name of the table from which we want to retrieve data.

• ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...: Sorts the result set in either ascending or descending order based on the specified columns.

Sorting in Ascending Order

By default, the Order by clause sorts the result set in ascending order. To sort the result set in ascending order, we do not need to specify the ASC keyword explicitly.

Example-1

SELECT * FROM customers
ORDER BY customer_name;

In this example, the result set will be sorted in ascending order based on the customer_name column.

Example-2

Suppose we have a table named "Employees" with the following columns and data −

EmployeeID	EmployeeName	Department	Salary
1	John	IT	50000
2	Jane	Sales	45000
3	Bob	IT	55000
4	Alice	HR	40000
5	Tom	HR	42000

If we want to retrieve all the data from the Employees table and sort it in ascending order by the Salary column, the SQL query would be −

SELECT * FROM Employees
ORDER BY Salary;

The output of the above query would be −

EmployeeID	EmployeeName	Department	Salary
4	Alice	HR	40000
5	Tom	HR	42000
2	Jane	Sales	45000
1	John	IT	50000
3	Bob	IT	55000

Sorting in Descending Order

To sort the result set in descending order, we need to specify the DESC keyword after the column name.

Example-1

SELECT * FROM customers
ORDER BY customer_name DESC;

In this example, the result set will be sorted in descending order based on the customer_name column.

Example-2

If we want to retrieve all the data from the Employees table and sort it in descending order by the Department column, the SQL query would be −

SELECT * FROM Employees
ORDER BY Department DESC;

The output of the above query would be

EmployeeID	EmployeeName	Department	Salary
2	Jane	Sales	45000
1	John	IT	50000
3	Bob	IT	55000
5	Tom	HR	42000
4	Alice	HR	40000

Sorting by Multiple Columns

We can also sort the result set by multiple columns. In this case, the Order by clause first sorts the result set based on the first column and then sorts it based on the second column.

Example-1

SELECT * FROM customers
ORDER BY country, customer_name;

In this example, the result set will be sorted in ascending order based on the country column. If two or more rows have the same country, then those rows will be sorted in ascending order based on the customer_name column.

Example-2

If we want to retrieve all the data from the Employees table and sort it first in ascending order by the Department column, and then in ascending order by the Salary column, the SQL query would be −

SELECT * FROM Employees
ORDER BY Department DESC;

The output of the above query would be

EmployeeID	EmployeeName	Department	Salary
2	Jane	Sales	45000
1	John	IT	50000
3	Bob	IT	55000
5	Tom	HR	42000
4	Alice	HR	40000

Sorting by Multiple Columns

We can also sort the result set by multiple columns. In this case, the Order by clause first sorts the result set based on the first column and then sorts it based on the second column.

Example-1

SELECT * FROM customers
ORDER BY country, customer_name;

In this example, the result set will be sorted in ascending order based on the country column. If two or more rows have the same country, then those rows will be sorted in ascending order based on the customer_name column.

Example-2

If we want to retrieve all the data from the Employees table and sort it first in ascending order by the Department column, and then in ascending order by the Salary column, the SQL query would be

SELECT * FROM Employees
ORDER BY Department ASC, Salary ASC;

The output of the above query would be

EmployeeID	EmployeeName	Department	Salary
4	Alice	HR	40000
5	Tom	HR	42000
1	John	IT	50000
3	Bob	IT	55000
2	Jane	Sales	45000

These examples demonstrate how the Order by clause can be used to sort the result set of a query in MS SQL Server.

Sorting with NULL Values

When we use the Order by clause, NULL values are sorted differently depending on whether we are sorting in ascending or descending order. In ascending order, NULL values are displayed first, while in descending order, NULL values are displayed last.

For example

SELECT * FROM customers
ORDER BY city DESC;

In this example, the result set will be sorted in descending order based on the city column. NULL values will be displayed last.

Conclusion

The Order by clause in MS SQL Server is a powerful tool that allows us to sort the result set of a query in either ascending or descending order based on one or more columns. By understanding the syntax and usage of the Order by clause, we can create more complex and meaningful queries to retrieve data from our database.