Order By in SQL - [with 5 Examples]

Introduction

In SELECT statement, the order of rows in the result is not guaranteed. It means that SQL Server return a result set with an unspecified order of rows or you can say sorting of rows. To sort the data in specific order, you can use the ORDER BY in SQL as ASC / DESC order.

Why you need to use ORDER BY in SQL?

When you execute SQL query with specific result, sometime you want to see the data in sorted order, example rank of students, sort the hotel room cost by highest or lowest cost etc. Then you can use Order by in SQL using ASC/Desc keyword to sort the data in specific order.

What is Order by ASC ?

ASC = Ascending order meaning, sort the result/data with specific column from the lowest value to the highest value (like 1, 2, 3 ,4 ,5 ,6 ,7 ,8 …..so on)

What is Order by DESC ?

DESC = Descending order meaning, sort the result/data with specific column from the highest value to the lowest one (like 19, 18, 17, 16, 15, 14, 13, 12, 11, 10, 9 …..so on)

Syntax:

SELECT
    select_list
FROM
    table_name
ORDER BY ASC|DESC

use ASC or DESC to specify, whether the values in the specified column should be sorted in ascending or descending order

ASC sorts the result from the lowest value to the highest value

DESC sorts the result set from the highest value to the lowest one

Note:
SQL Server uses ASC as the default sort order

Example #1: Order by ASC, Sort one column in ascending order

If you are not using any specified order it will show the default order that is Ascending order. this default order is applicable for any generic query.

as shown below example the output will show result for Customer name in ascending order

SELECT
      Customer_Name
FROM
     [dbo].[Customers_Tbl]
ORDER BY
     Customer_Name

Result:

Example #2: ORDER BY DESC, Sort one column in Descending order

In this example we have used DESC, which tell the SQL query to give the result of records in descending order.

SELECT
      Customer_Name
FROM
     [dbo].[Customers_Tbl]
ORDER BY
     Customer_Name DESC

Result:

Example #3: Order by / Sort multiple columns with different orders

There is way where you can use both orders ASC | DESC, If you are using to sort the records based on two column, As sometime we need to show data in order of first column and then by second column.

you can do that by using below example. In this SQL query is performed on Customer_Name and Product_Category to do the sort DESC and ASC respectively

SELECT
       Customer_Name, Product_Category
FROM
       [dbo].[Customers_Tbl]
ORDER BY
       Customer_Name DESC,
       Product_Category ASC

Result:

Example #4: Sort a column based on function

you can apply ASC/DESC option in any function in query, example Len(), Left (), Right() etc…

As shown below example, which try to sort the “Customer_Name” column based on first name using Substring function

SELECT     
     Customer_Name, 
     Product_Category 
FROM    
     [dbo].[Customers_Tbl] 
ORDER BY        
     SUBSTRING(Customer_Name , 1, CHARINDEX(' ', Customer_Name ) - 1) ASC

Example #5: Sort a column using WHERE clause

In below example, sort the Customer_Name column based on WHERE clause as condition on Product_Category. Result will show the sorted column for Customer_Name for all Product_Category equal to ‘Furniture’.

SELECT    
     Customer_Name, 
     Product_Category
FROM   
     [dbo].[Customers_Tbl]
WHERE
     Product_Category = 'Furniture'
ORDER BY         
     Customer_Name ASC

Order By in SQL (ASC and Desc)