# 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
```