Using order by in union of queries

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Using order by in union of queries

Author

Topic

shantanu88d
Starting Member

35 Posts

Posted - 2011-04-21 : 01:41:36

I have a table

carrier
--------------
Aircel
Airtel
Vodafone
Virgin Mobiles
Uninor
Idea
Docomo
MTS
MTNL
BSNL
Dolphin

I want to populate a dropdownList with these carriers, however i want another element ---Select--- to appear above them. I tried


SELECT '---SELECT---'
UNION
SELECT * 
FROM carriers

This works. But carriers are not in order. When I do this


SELECT '---SELECT---'
UNION
SELECT * 
FROM carriers
ORDER BY carrier

I get error:
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.

How to order these elements and include ---SELECT--- at the top of them using a query.
Plz help !!!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-04-21 : 02:02:39

This should work:

select * from
(
SELECT '---SELECT---' as item
UNION
SELECT *
FROM carriers
)dt
order by case when item = '---SELECT---' then char(32) else item end

No, you're never too old to Yak'n'Roll if you're too young to die.