SQL Server – User Defined Sort Order

Varinder Sandhu SQL, SQL-Advanced, SQL-General, SQL-Query, SQL-Scripts, SQL-Tips and Tricks, Varinder Sandhu

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

Use Defined Sort Order

Requirement:

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

REQUIRED OUTPUT:

Use Defined Sort Order

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

Use Defined Sort Order

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:

Use Defined Sort Order

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: