Can we perform order by on more than 2 columns in SQL Server

Question

Bit confused if we can perform order by on more than two columns.

Here is the result:-

Select * 
from orderbytest 
order by FName desc, LName desc, YearOfBirth desc

Resulting output:

FName     LName         YearOfBirth
------------------------------------
Thomas    More          1478
Thomas    Jefferson     1826
Thomas    Alva Edison   1847
Benjamin  White         1600
Benjamin  Franklin      1706

Ok so why are you confused?The results would be easier to understand if you had duplicates in the LName column also. — Mihai, Aug 06 '15 at 06:31
Don't be confused. Keep calm and try understand code. See good:) — starko, Aug 06 '15 at 06:34
see this [answer](http://stackoverflow.com/questions/2051162/sql-multiple-column-ordering) — Praveen, Aug 06 '15 at 06:34

score 1 · Accepted Answer · answered Aug 06 '15 at 06:34

Short answer - yes.

When applying multiple order by expressions the results are sorted by the first one. When multiple rows have the same value in the first order by expression, they are sorted internally by the second one. When multiple rows have the same value in the first and second order by expressions they are sorted by the third one, and so on.

Here, all your rows have a unique combination of the first two expressions, so the third one, while still valid, is inconsequential.

I guess i understood it.Thank you :) – Phoenix Aug 06 '15 at 07:26 — Phoenix, Aug 06 '15 at 07:26

sulhadin · Answer 2 · 2015-08-06T06:38:27.293

0

Yes you can perform however you like Just like Grouping.

here;

Select * from orderbytest order by FName ,LName asc,YearOfBirth desc

Not that, when you do not write any keyword after a field the result will list in the default order.

edited Aug 06 '15 at 06:38

answered Aug 06 '15 at 06:33

sulhadin

521
4
16

1

Just to be clear: default sort order is **asc** - so this sorts by FName **asc**, LName **asc** and only by YearOfBirth **desc** - not like what the OP has in his question. – marc_s Aug 06 '15 at 06:36
Yes sir I missed something and I edited the post. Thank you for warning. – sulhadin Aug 06 '15 at 06:39

score 0 · Answer 3 · edited Aug 06 '15 at 07:24

0

Yes you can. try this query for understanding order by more then one column:

select *
from
(
    select 'ashish' col1,1 col2
    union all
    select 'ashish',3
)base
order by col1 asc, col2 desc

edited Aug 06 '15 at 07:24

Sabyasachi Mishra

1,677
2
31
49

answered Aug 06 '15 at 06:39

Ashish Kumar

157
1
7

score 0 · Answer 4 · answered Aug 06 '15 at 06:42

This will group in Descending order of FName , LName , YearOfBirth

SELECT        FName, LName, YearOfBirth
FROM            orderbytest
ORDER BY FName DESC, LName DESC, YearOfBirth DESC

For ascending order :

SELECT        FName, LName, YearOfBirth
FROM            orderbytest
ORDER BY FName , LName , YearOfBirth

Can we perform order by on more than 2 columns in SQL Server

4 Answers4