CODEWITHSUNDEEP
sqlsql-serversql-order-byunion
Dhaval Ptl
Dhaval Ptl

Reputation: 494

Order by clause with Union in Sql Server

I want List of party names with 1st option as 'All' from database. but i won't insert 'All' to Database, needs only retrieve time. so, I wrote this query.

Select 0 PartyId, 'All' Name
Union
select PartyId, Name
from PartyMst

This is my Result

0   All
1   SHIV ELECTRONICS
2   AAKASH & CO.
3   SHAH & CO.

when I use order by Name it displays below result.

2   AAKASH & CO.
0   All
3   SHAH & CO.
1   SHIV ELECTRONICS

But, I want 1st Option as 'All' and then list of Parties in Sorted order. How can I do this?

Upvotes: 15

Views: 31566

Answers (3)

Himanshu
Himanshu

Reputation: 32602

You need to use a sub-query with CASE in ORDER BY clause like this:

SELECT * FROM
(
  Select 0 PartyId, 'All' Name
  Union
  select PartyId, Name
  from PartyMst
) tbl
ORDER BY CASE WHEN PartyId = 0 THEN 0 ELSE 1 END
,Name

Output:

PARTYID NAME
0 All
2 AAKASH & CO.
3 SHAH & CO.
1 SHIV ELECTRONICS

See this SQLFiddle

Upvotes: 40

Daniel De León
Daniel De León

Reputation: 13639

You can use 'order by' with the top instrucction in a union in this way:

Select 0 PartyId, 'All' Name
Union
select * from ( 
  select top (select count(*) from PartyMst) PartyId, Name
  from PartyMst
  order by Name
)

Upvotes: 1

Raj
Raj

Reputation: 10843

Since you are anyway hardcoding 0, All just add a space before the All

Select 0 PartyId, ' All' Name
Union
select PartyId, Name
from PartyMst
ORDER BY Name

SQL FIDDLE

Raj

Upvotes: 1

Related Questions