Using sql union and order by

Posted on 2013-02-05
Last Modified: 2013-02-05
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
select id, (lastname + ',' + firstname) as "Fullname"
from employee
--order by

Please assist
Question by:Sirdots
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 38855317
select '' as id,
'--select--'  as lastname, 0 as SortOrder
select id, (lastname + ',' + firstname) as "Fullname", 1 as SortOrder
from employee
--order by
SortOrder -- then the rest of your order by.
LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 500 total points
ID: 38855319
this trick will do:
select id, lastname 
 from  (select 0 ob, '' as id, '--select--'  as lastname
select 1, id, (lastname + ',' + firstname) as "Fullname"
from employee
) sq
order by ob, lastname 

Open in new window

LVL 15

Expert Comment

ID: 38855326
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
select id, (lastname + ',' + firstname) as "Fullname", 2
from employee
--order by
   OrderCol, ID
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38855327
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)

Author Closing Comment

ID: 38855890

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL - format decimal in a string 5 51
Add a step to a system backup job 6 36
Parse this column 6 35
Connect to SQL 2008 r2 server over the Internet 4 19
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question