ORDER BY Clause in SQL Server

In this SQL Server tutorial, you will learn about the ORDER BY clause in SQL Server, where you will sort the data in a specific order.

You will understand the general syntax of the ORDER BY clause that you can use anywhere in your SQL Server query. Then, you will order the rows in ascending or descending order.

Afterwards, you will order the aggregate result using the ORDER BY clause.

Table of Contents

Whenever you run the query in SQL Server, you get a result set based on your query, and the result set also contains some rows, but have you ever thought about the order of the rows in the result set?

You can use the ORDER BY clause to order the rows in your query result set. If you want to present the result set data in a specific order, such as ascending or descending, then you can order the result set data using the ORDER BY clause in SQL Server.

But let me explain some scenarios where the ORDER BY clause provides invaluable.

You can sort the financial data like expenses or revenues to determine movements or order sales data by date and region to analyze sales performance over time.
In the application, you can show the sorted list of items such as products, users, and transactions based on the chosen criteria such as price, name or date.

The syntax is given below.

SELECT column_1, column_2, ...
FROM table_name
ORDER BY column_1 ASC|DESC, column_2 ASC|DESC

Where,

SELECT column_1, column_2: These are the columns you want to fetch from the table.
FROM table_name: The table name from which you want to retrieve the information or data.
ORDER BY column_1 ASC|DESC, column_2 ASC|DESC: The columns by which you want to sort and the sorting direction, such as ASC (for ascending order) and DESC (for descending order). By default, results are ordered in ASC.

Let’s take an example where you will see how to sort the column values in ascending order. You have the SalesData table as shown below.

ORDER BY Clause in SQL Server with SalesData Table

In ascending order, you must show the SalesData table data based on the SalesAmount column. For that, use the query below.

SELECT * FROM SalesData
ORDER BY SalesAmount ASC;

As you can see, the SalesAmount column values are sorted in ascending order (from the lowest sale amount to the highest). For example, the sale amounts for Product IDs with 102 are 300.00, 350.00, and 400.00.

In the query part ORDER BY SalesAmount ASC, change the order of the rows in the result to ascending order based on the SalesAmount column values because the ORDER BY clause arranges all the values of the SalesAmount column from lowest sale amount to highest sale amount.

This is how to use the ORDER BY clause in SQL Server to order the rows in a result set of queries.

ORDER BY Clause in SQL Server in DESC Order

The ORDER BY clause allows you to order the results data alphabetically in descending order ( from highest value to lowest value or letter Z to A) by selecting the keyword DESC.

For example, let’s use the same SalesData table you used in the above section. Again, order the SalesAmount column values, but this time in descending order using the query below.

SELECT * FROM SalesData
ORDER BY SalesAmount DESC;

ORDER BY Clause in SQL Server in DESC Order

As you can see, the SalesAmount column values are sorted in descending order (from the highest sale amount to the lowest). For example, the sale amounts for Product IDs with 101 is 1200.00, 103 is 1100.00, and so on.

In the query part ORDER BY SalesAmount DESC, changes the order of the rows in the result to descending order based on the SalesAmount column values because the ORDER BY clause arranges all the values of the SalesAmount column from the highest sale amount to the lowest sale amount.

Also, remember that you have to order the rows based on the single column here, so if you want to order the result set data based on more than one column, then specify that column as you have specified for the single.

This is how to use the ORDER BY clause in SQL Server to order the rows in descending order.

ORDER BY Clause in SQL Server with Aggregated Result

Sometimes, you need to get an aggregated result set in a specific order; you can use the ORDER BY clause to sort the aggregated results.

Suppose you must find the total sales per region and sort these regions by total sales; for that, use the below query.

SELECT Region, SUM(SalesAmount) AS TotalSales
FROM SalesData
GROUP BY Region
ORDER BY TotalSales DESC;

ORDER BY Clause in SQL Server with Aggregated Result

From the output of the above query, the result set contains the four regions, North, South, West and East, with total sales of 4200.00, 3100.00, 3040.00 and 2830.00, respectively.

In the query part, the data is first grouped by region column using the GROUP BY region, and then total sales are computed per region or group using the SUM(SalesAmount); the SUM() is the aggregate function. Then, query part ORDER BY TotalSales DESC sorts (order) the regions (aggregated result) by their total sales in descending order.

The TotalSales column in the result set contains the sale value in descending order, from the highest (4200.00) total sales to the lowest (2830.00) total sales.

This is how to ORDER BY Clause in SQL Server with Aggregated Result.

Conclusion

In this SQL Server tutorial, you learned how to order the result set data using the ORDER BY clause in SQL Server, and you ordered the result set in ascending or descending order. Also, you ordered the aggregated results in descending order.