Solved

Using sql union and order by

Posted on 2013-02-05
5
349 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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
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…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

747 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now