Unexpected behaviour of Order by clause

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

How to do it?

Thanks in advance

Answer 1

That sort order is correct.

It's sorted first by col1, then by col2 (within any duplicates in col1), etc...

Notice how for same value in col2, col2 is in ascending order:

Col1    col2    col3    col4    col5 
1       4         0     3       7 
1       5         1     4       6 
1       8         2     1       5 

Here's another example. Imagine we have a list of Lastname, Firstname:

Smith, Ian
Smith, John
Smith, Dave
Smith, Jane
Green, Jim

Sorted by Lastname ASC, Firstname ASC that would be:

Green, Jim
Smith, Dave
Smith, Ian
Smith, Jane
Smith, John

Answer 2

Columns are not independent of their row -- sort order applies to the whole row, not to individual columns. All data within a row is understood to be related -- you cannot individually slice/dice/rearrange data that violates this.

You can't do what you want to do in the current structure you have.