Multiple column sorting (SQL Server 2005)

Question

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 the 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

Answer 1

The sort you're trying to do will "rip apart" the row that you had before - not something you should do in relational databases....

E.g. the last line in your original table, you have a tuple (4, 3, 2, 1, 4) - but suddenly, the result you're looking for, you have a tuple (4, 8, 5, 6, 7).

This is very odd and contradicts every "usual" way of dealing with data. A RDBMS typically has tuples (rows) of data, and you can sort those by any number of columns - but you cannot suddenly create new tuples after a sort operation. Very weird....

I would recommend you rethink your entire strategy of doing this - it feels very strange and at odds with any of the usual database principles I know..... what exactly (and why exactly) are you trying to achieve this?? Seems you're really dealing with a collection of disparate data - so why don't you put those disparate chunks of data into their own tables, if they really don't have anything to do with one another??

Answer 2

I totaly agree with everyone else. Purely as a thought exercise, I tried to puzzle out how to do this, and realized that anything I came up with would look very much like the solution you are not happy with.

You'd have to break the table into one table per column, sort those columns, and then form new columns by joining on the sort order -- which is precisely what you do. I see no shortcuts to this because, as has been said, relational databases (let alone relational database theory) are simply not designed to support the kind of manipulations to tuples (rows) that your "seniors" want you to perform.

Remember, just because they're older than you, have "job-seniority" over you, and (apparently) have been doing it for longer than you doesn't (a) make them right or (b) smart.

Answer 3

(It sounds like this could be a result from a spreadsheet with independent columns that has been imported but the columns were not correctly sorted...?)

I agree that this sounds like a strange request because it's breaking ties between values in the same row. Normally values in the same row have strong logical bonding defined by athe entity the row describes. Separating them and taking values in isolation usually makes the information meaningless, or shows that the data did not need to be stored together in a table to begin with. If the latter is the case, then the table would be much more usable split into several tables.

And it's because the RDBMS model fundamentally wants to return values from the same row that you have to break apart the table as you have done. Structurally there are no alternatives - only different ways of writing the query to achieve the same result.

Can I say, I think you've written a good query - I've interviewed many candidates who could not have solved this problem as you have. For you to be able to do your job well, your job-seniors should at least explain to you why the data is like this, and whether changing table structure is an option if that would bring significant improvements to the system.