Sort Order with Multiple Levels Using T-SQL


  

  CREATE TABLE [dbo].[test](
	[Lev1] [char](1) NULL,
	[LeV2] [char](2) NULL,
	[Lev3] [int] NULL,
	[Price] [int] NULL
)

INSERT [dbo].[test] ([Lev1], [LeV2], [Lev3], [Price]) VALUES  (N'A', NULL, NULL, 80)
,(N'A', N'1 ', NULL, 30)
,(N'A', N'1 ', 1234, 10)
,(N'A', N'1 ', 1235, 5)
,(N'A', N'1 ', 1236, 15)
,(N'A', N'2 ', NULL, 50)
,(N'A', N'2 ', 1237, 20)
,(N'A', N'2 ', 1238, 5)
,(N'A', N'2 ', 1239, 25)
,(N'B', NULL, NULL, 90)
,(N'B', N'1 ', NULL, 30)
,(N'B', N'1 ', 1240, 30)
,(N'B', N'2 ', NULL, 60)
,(N'B', N'2 ', 1242, 10)
,(N'B', N'2 ', 1243, 50)

;with mycte as (
SELECT[Lev1], [LeV2], [Lev3],[Price]
	  ,dense_rank() Over( Order by [Lev1] desc) rn1
	  ,dense_rank() Over( Order by [Lev1] desc,Case when Lev2 is null then 9999 else Lev2 end  desc) rn2
	  ,dense_rank() Over( Order by [Lev1] desc,Lev2 desc,Case when Lev3 is null then 9999 else Lev3+Price end desc) rn3
  FROM  [dbo].[test]
  )

SELECT [Lev1], [LeV2], [Lev3],[Price]
--, rn1, rn2, rn3
FROM   mycte
ORDER  BY rn1, rn2, rn3 

---https://social.msdn.microsoft.com/Forums/en-US/13b0c16a-1543-4d3c-9fea-60acebc31eff/sql-order-by-query-accross-multi-level-groups?forum=transactsql
 /*
 Lev1	LeV2	Lev3	Price
B	NULL	NULL	90
B	2 	NULL	60
B	2 	1243	50
B	2 	1242	10
B	1 	NULL	30
B	1 	1240	30
A	NULL	NULL	80
A	2 	NULL	50
A	2 	1239	25
A	2 	1237	20
A	2 	1238	5
A	1 	NULL	30
A	1 	1236	15
A	1 	1234	10
A	1 	1235	5
 */

--Another solution
select Lev1,Lev2, Lev3, Price from test
Order by Cast(Lev1 as binary(8)) desc
, Cast(Case when Lev2 is null then 9999 else Lev2 end as binary(8)) desc
, Cast(Case when Lev3 is null then 9999 else Lev3 + Price end as binary(8)) desc

drop table test
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s