Beginners guide: How to Sort Results In SQL Server

Neerajprasadsharma
Posted by in Sql Server category on for Beginner level | Points: 250 | Views : 404 red flag

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






Page copy protected against web site content infringement by Copyscape

About the Author

Neerajprasadsharma
Full Name: NEERAJ Sharma
Member Level: Starter
Member Status: Member
Member Since: 5/13/2016 8:42:37 AM
Country: India
I write technical articles mainly on SQL Server Query Optimizer for more information look at my BIO.

Neeraj Prasad Sharma is a SQL Server developer who started his work as a dot net programmer. He loves SQL Server query optimizer`s capability to process the queries optimally. For the last six years he has been experimenting and testing Query Optimizer default behaviour and if something goes wrong his goal is to identify the reason behind it and fix it. I write technical article here: https://www.sqlshack.com/author/neeraj/ https://www.codeproject.com/script/Articles/MemberArticles.aspx?amid=12524731 https://www.mssqltips.com/sqlserverauthor/243/neeraj-prasad-sharma-/

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)