How to use Sorting ( Order By) in Select Statement in SQL Server - SQL Server / TSQL Tutorial Part 109

Scenario:

You are working as SQL Server developer, you need to output sorted data. Which Key words you would like to use in T-SQL To output sorted data?

Solution:

The Order by Keyword can be used to sort the result set. We can sort the results by single or multiple columns.

Let's create dbo.Customer table with couple of test records and then use Order by keyword to sort them.

  Create table dbo.Customer(
  id int, FName VARCHAR(100))
  insert into dbo.Customer values(
  1,'Aamir'),(2,'Raza')



1) When we only use Order by ColumnName
Let's say if you would like to sort the data by using Id column and in ascending order, you can use below query.

  Select * From dbo.Customer
  order by Id


  Select * From dbo.Customer
  order by Id ASC

Notice that in first query, I did not write ASC to order the data in ascending order. In second query I did use ASC key word. If you don't use any keyword after column names, SQL Server will order the data in ascending order.


How to use Order by to sort result set in SQL Server


2) How to sorting data in descending order
To order results in descending order, you have to add DESC keyword after column names. Let's say if we would like to order the data by using FName in descending oder, we will use below query.

  Select * From dbo.Customer
  order by FName DESC 
How to sort the output returned by select query by using Order by keyword


3)  Using Column Numbers instead of Column Names in Order by 
You can always use the Column numbers instead of column names in Order by. Let's say if we would like to order by FName,ID we can write our query by using names or column numbers as shown below.

   Select * From dbo.Customer
  order by FName DESC,id DESC
  
  Select * from dbo.Customer
  Order by 2 DESC,1 DESC

How to use Column numbers instead of using Column Names in Order By to sort results in SQL Server


No comments:

Post a Comment