#custom sort order

New Post has been published on http://www.varindersandhu.in/2011/10/24/sql-server-user-defined-sort-order/

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:

<!-- google_ad_client = "pub-2404605494811633"; google_alternate_color = "FFFFFF"; google_ad_width = 468; google_ad_height = 60; google_ad_format = "468x60_as"; google_ad_type = "text_image"; google_ad_channel =""; google_color_border = "FFFFFF"; google_color_link = "0000FF"; google_color_bg = "FFFFFF"; google_color_text = "000000"; google_color_url = "008000"; google_ui_features = "rc:6"; //-->

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:

Use Defined Sort Order

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