Custom Sorting in SQL SERVER

Today we are discussing about custom sorting in SQL SERVER

First of all we can create a simple table:

CREATE TABLE CustSortExampleTbl

(

id int primary key

,Name nvarchar(100)

,age int

)

Now we can enter some sample values to our table

insert into CustSortExampleTbl values(1,'Arun',24)

insert into CustSortExampleTbl values(2,'Jijo',25)

insert into CustSortExampleTbl values(3,'Prasanth',26)

insert into CustSortExampleTbl values(5,'Hareesh',20)

insert into CustSortExampleTbl values(4,'Nikhil',26)

insert into CustSortExampleTbl values(10,'Achu',22)

insert into CustSortExampleTbl values(8,'Santhosh',24)

Now our table values look like below

Id    name        age

------------------------------------

1     Arun            24

2     Jijo              25

3     Prasanth      26

4     Nikhil           26

5     Hareesh       20

8     Santhosh      24

10    Achu           22

If we need to sort the details based on id then we simply do

1)Select * from CustSortExampleTbl order by id desc

Or

2)Select * from CustSortExampleTbl order by id asc

First query will sort the details in descending order and the Second query will sort it in ascending order .This is the most commonly sorting we are used, also we used some sorting using case we can look on that too .

Example :

Select * from CustSortExampleTbl order by (case when id=5 then null else id end)

The above query will results data like below

id    Name        age

-------------------------

5     Hareesh     20

1     Arun          24

2     Jijo            25

3     Prasanth    26

4     Nikhil         26

8     Santhosh    24

10    Achu         22

The interesting this now we want to sort the details in some custom order, that is we define the order and we need to sort the data in that order.

Example :

Id    name        age

----------------------

3     Prasanth    26

4     Nikhil         26

10    Achu         22

5     Hareesh     20

1     Arun          24

2     Jijo            25

8     Santhosh   24

Look at the above result I need to sort the data like above

That is in a custom id order like : 3,4,10,5,1,2,8

How can we do this ??

Very simple look at the following query

SELECT *

FROM CustSortExampleTbl Cst

WHERE id IN (3,4,10,5,1,2,8)

ORDER BY CHARINDEX(','+CONVERT(varchar, Cst.id)+',', ',3,4,10,5,1,2,8,')

Result :

id    Name        age

---------------------------

3     Prasanth    26

4     Nikhil         26

10    Achu         22

5     Hareesh     20

1     Arun          24

2     Jijo            25

8     Santhosh    24