Multiple column sorting (SQL SERVER 2005)

Posted by Newbie on Stack Overflow See other posts from Stack Overflow or by Newbie
Published on 2010-05-24T04:19:47Z Indexed on 2010/05/24 4:31 UTC
Read the original article Hit count: 194

Filed under:

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

© Stack Overflow or respective owner

Related posts about sql-server-2005