Order on Second Select of Using Statement

9 Mar 2006 10:37 PM

Eyeless621

I'm trying to combine two select statements with "USING", but I only want the
second statement to be sorted.
I have a Table like:

Name
---
D
C
B
A

and I want B to be the first value returned and everything else to be
sorted. My query right now is:

SELECT Name FROM Table WHERE Name = 'B'
UNION
SELECT Name FROM Table WHERE Name != 'B' ORDER BY Name

This gives me:
A
B
C
D

It seems it does the union of the two select queries before ordering them,
is there a way around it so the results are:
B
A
C
D

Thanks for any help!!

9 Mar 2006 11:01 PM

Erland Sommarskog

=?Utf-8?B?RXllbGVzczYyMQ==?= (Eyeless***@discussions.microsoft.com) writes:
Show quote

> I'm trying to combine two select statements with "USING", but I only
> want the second statement to be sorted. > I have a Table like:
>
> Name
> ---
> D
> C
> B
> A
>
> and I want B to be the first value returned and everything else to be
> sorted. My query right now is:
>
> SELECT Name FROM Table WHERE Name = 'B'
> UNION
> SELECT Name FROM Table WHERE Name != 'B' ORDER BY Name
>
> This gives me:
> A
> B
> C
> D

ORDER BY CASE Name WHEN 'B' THEN NULL ELSE Name

And you don't even need UNION for this.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

9 Mar 2006 11:16 PM

Eyeless621

That works great, much easier than I thought... Thanks a lot!

Show quote

"Erland Sommarskog" wrote:

> =?Utf-8?B?RXllbGVzczYyMQ==?= (Eyeless***@discussions.microsoft.com) writes:
> > I'm trying to combine two select statements with "USING", but I only
> > want the second statement to be sorted. > I have a Table like:
> >
> > Name
> > ---
> > D
> > C
> > B
> > A
> >
> > and I want B to be the first value returned and everything else to be
> > sorted. My query right now is:
> >
> > SELECT Name FROM Table WHERE Name = 'B'
> > UNION
> > SELECT Name FROM Table WHERE Name != 'B' ORDER BY Name
> >
> > This gives me:
> > A
> > B
> > C
> > D
>
>
> ORDER BY CASE Name WHEN 'B' THEN NULL ELSE Name
>
> And you don't even need UNION for this.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>