CODEWITHSUNDEEP
sqlsql-serversql-server-2008
codrgi
codrgi

Reputation: 29

order different columns into one order?

I have a table like this;

Student_Name1   mark1  Student_Name2    mark2
-------------- ------ ---------------  --------
Kevin           77       Peter          78
Andrew          91       David          17
Scott           46       Bradley        28

How am i able to order mark1 and mark2 in the above table into descending order by including all the names and the points all together, like below?

Student_Name   mark
-------------- ------
Andrew          91
Peter           78
Kevin           77
Scott           46
Bradley         28
David           17

I am using MSSQL Server 2008 R2

Upvotes: 1

Views: 43

Answers (2)

Rahul
Rahul

Reputation: 77856

That's a strange table design but you can use UNION for this purpose like

select * from (
select Student_Name1 as Student_Name,  mark1 as mark from student
union all
select Student_Name2 ,  mark2 from student ) xxx
order by mark desc;

Upvotes: 3

Siyual
Siyual

Reputation: 16917

Use a UNION ALL:

Select    Student_Name1 As Student_Name,
          Mark1         As Mark
From      YourTable
Union All
Select    Student_Name2 As Student_Name,
          Mark2         As Mark
From      YourTable
Order By Mark Desc;

Upvotes: 4

Related Questions