Using sql union and order by

I have this query and plan to use it to populate a drop down box. I want the --Select-- option to be the first on the list and I still want to be able to sort by lastname ascending. Right now when I sort it is not making the  --select-- the first item.


select '' as id,
'--select--'  as lastname
union
select id, (lastname + ',' + firstname) as "Fullname"
from employee
--order by

Please assist
SirdotsAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
this trick will do:
select id, lastname 
 from  (select 0 ob, '' as id, '--select--'  as lastname
union
select 1, id, (lastname + ',' + firstname) as "Fullname"
from employee
) sq
order by ob, lastname 

Open in new window

0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
select '' as id,
'--select--'  as lastname, 0 as SortOrder
union
select id, (lastname + ',' + firstname) as "Fullname", 1 as SortOrder
from employee
--order by
SortOrder -- then the rest of your order by.
0
 
tim_csCommented:
Add an extra column.  Something like OrderCol and make it a 1 for the first and a 2 for the second query.

select '' as id, 1 AS OrderCol
'--select--'  as lastname
union
select id, (lastname + ',' + firstname) as "Fullname", 2
from employee
--order by
   OrderCol, ID
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
also please use UNION ALL instead of UNION, that will avoid a implicit DISTINCT to be performed (which in this case would just be a waste of resources)
0
 
SirdotsAuthor Commented:
Thanks.
0
All Courses

From novice to tech pro — start learning today.