Using sql union and order by

Posted on 2013-02-05
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 2000 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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

741 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