Solved

Using sql union and order by

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

Please assist
0
Comment
Question by:Sirdots
5 Comments
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 38855317
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
 
LVL 142

Accepted Solution

by:
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
union
select 1, id, (lastname + ',' + firstname) as "Fullname"
from employee
) sq
order by ob, lastname 

Open in new window

0
 
LVL 15

Expert Comment

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

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)
0
 

Author Closing Comment

by:Sirdots
ID: 38855890
Thanks.
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article I will describe the Copy Database Wizard 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 Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

777 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