SQL Server – User Defined Sort Order

I had requirement to get the values with Use Defined Sort order. Here is simple way to get User Defined Sort order

Example:

Created a table as :

CREATE TABLE sort_order
(
id INT,
dept VARCHAR(20),
detail VARCHAR(30)
)

Inserted few rows in table as

Requirement:

Now we want to change the sort order and new sorted output should be:

REQUIRED OUTPUT:

For this created a function:

CREATE FUNCTION test_fn
(
)
RETURNS @temp_table TABLE
(
    sno int,
    dept varchar(20)
)

AS

BEGIN

	INSERT @temp_table(sno,dept)
	SELECT 1,'C'
	UNION all
	SELECT 2,'A'
	UNION all
	SELECT 3,'D'
	UNION all
	SELECT 4,'E'
	UNION all
	SELECT 5,'B'

  RETURN
END

Now for the desired or  User Defined sort order we ran the following query:

SELECT so.dept,so.detail
FROM sort_order so
JOIN
(
	SELECT * FROM test_fn()
) aa
ON aa.dept = so.dept
ORDER BY aa.sno

 

We get the REQUIRED OUTPUT as

SPECIAL CASES

Now on inserting a new record in the table sort_order and not making an entry of the same in the test_fn() will not display it int the result using the above query.

Hence to handle this we modify the query as :

select Isnull(aa.sno,99) as SNo, so.dept,so.detail from sort_order so
Left join
(
	Select * from test_fn()
) aa
on aa.dept = so.dept
ORDER BY AA.sno

This will replace the NULL provided earlier by 99 so that we get to know that this is the new record.

OUTPUT:

NOTE: But in this the sorting order has changed. We require 99 record to come at the end. This can be handled in two ways:

CASE 1

SELECT SNo,dept,detail
FROM
(
select CAST (Isnull(aa.sno,99)AS int) as SNo, so.dept,so.detail
from sort_order so
Left join
(
	select * from test_fn()
) aa
on aa.dept = so.dept 

) BB
ORDER BY BB.SNo

 

CASE 2

We can handle the sorting using Reporting Tool like Crystal Report

OUTPUT:

If you have any other way, you can share as comment.

Thanks to my colleague Bhavna Kakkar who documented this post for me.