Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Using sql union and order by

Posted on 2013-02-05
5
Medium Priority
?
364 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
[X]
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
5 Comments
 
LVL 41

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 143

Accepted Solution

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

Author Closing Comment

by:Sirdots
ID: 38855890
Thanks.
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

636 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