Learn the basics of sorting in SQL Server
How to Sort Results In SQL Server
Sometimes we need to sort the table to view the data in some specific order. The tables contain data only, and to convert data into relevant information sometimes we need to sort it.
Order by can performed on datatypes in ascending or descending order.
For example, we have a table containing the results of an examination. Now we want to sort the students by their scoring marks, so we need to sort the table on the marks`s column using Order By Clause.
Let us continue the example and create a table containing data of a result table.
Create table Result (Roll_No int, Name varchar(50) , DOB datetime , Totalmarks int )
Insert Into Result
Select 1 , 'Neeraj' , '3-feb-1986' , 60 Union all
Select 2 , 'Vicky' , '12-Mar-1987' , 76 Union all
Select 3 , 'Bob' , '12-Mar-1987' , 76 Union all
Select 4 , 'Roger' , '1-Jan-1980' , 90
Sort (Order By) numeric
Select * from Result Order By TotalMarks desc
Look at the result set above, now the result set is sorted on the basis of TotalMarks column, because we need to view the data in ascending order, we have used ‘Asc‘ after the column.
We can sort other data type as well. At our table, we have Name and DOB column those have varchar and datetime datatype respectively. See the below example:
Select * from Result Order By DOB DESC
Select * From Result Order By name ASC
You can see in the above result set how it is sorted in descending and ascending order based on DOB and Name column.
Sort More Than One Column
Sometimes we need to sort more than one column in the result set, we can perform this in SQL Server as well, just need to specify the column name and their desired order, separated by comma (,) in the order by clause, if we do not specify the order direction ascending or descending it by default use the ascending order. Look at the example below:
Select * from Result
Order By TotalMarks desc , Name asc
User Defined Sort in SQL Server
Sometimes a user wants to see the data in pre-specified order, we can use the case expression to do sort the result set defined by the user.
For example, a user wants to see the name ‘BOB’ first, then ‘Vicky’ second and rest result according to roll number in ascending order. How would you fulfill the request example below using case expression:
Select * from Result Order By (Case When Name = 'BOB' Then 1
When Name = 'Vicky' Then 2
ELSE 3 END ), ROLL_NO