Multiple column sorting (SQL SERVER 2005)
- by Newbie
I have a table which looks like
Col1 col2 col3 col4 col5
1 5 1 4 6
1 4 0 3 7
0 1 5 6 3
1 8 2 1 5
4 3 2 1 4
The script is
declare @t table(col1 int, col2 int, col3 int,col4 int,col5 int)
insert into @t
select 1,5,1,4,6 union all
select 1,4,0,3,7 union all
select 0,1,5,6,3 union all
select 1,8,2,1,5 union all
select 4,3,2,1,4
I want the output to be every column being sorted in ascending order i.e.
Col1 col2 col3 col4 col5
0 1 0 1 3
1 3 1 1 4
1 4 2 3 5
1 5 2 4 6
4 8 5 6 7
I already solved tye problem by the folowing program
Select
x1.col1
,x2.col2
,x3.col3
,x4.col4
,x5.col5
From (Select Row_Number() Over(Order By col1) rn1, col1 From @t)x1
Join(Select Row_Number() Over(Order By col2) rn2, col2 From @t)x2 On x1.rn1=x2.rn2
Join(Select Row_Number() Over(Order By col3) rn3, col3 From @t)x3 On x1.rn1=x3.rn3
Join(Select Row_Number() Over(Order By col4) rn4, col4 From @t)x4 On x1.rn1=x4.rn4
Join(Select Row_Number() Over(Order By col5) rn5, col5 From @t)x5 On x1.rn1=x5.rn5
But I am not happy with this solution.
Is there any better way to achieve the same? (Using set based approach)
If so, could any one please show an example.
Thanks
