ORDER BY before UNION syntax error

I have a list of Nationalities which I want to sort alphabetically except
for the value of Nationality which is "not disclosed" which I would like to
put at the top of the list.
I'm trying to do this with the following query but there seems to be a
problem with putting the ORDER BY in front of the UNION keyword
:
SELECT TOP 100 PERCENT NationalityID, Nationality
FROM dbo.Nationality
WHERE (Nationality <>'not disclosed')
ORDER BY Nationality
UNION
SELECT TOP 100 PERCENT NationalityID, Nationality
FROM dbo.Nationality
WHERE (Nationality = 'not disclosed')
I tried using brackets around the first part of the query but that didn't
work.
Any help much appreciated.
PeteHi
You can add an extra columns (examples are not tested!)
SELECT TOP 100 PERCENT NationalityID, Nationality, 0 as OrderBy
FROM dbo.Nationality
WHERE Nationality <>'not disclosed'
UNION
SELECT TOP 100 PERCENT NationalityID, Nationality, 1
FROM dbo.Nationality
WHERE Nationality = 'not disclosed'
ORDER BY OrderBy, Nationality
Although you do not need a UNION in this example:
SELECT DISTINCT NationalityID, Nationality, CASE WHEN Nationality = 'not
disclosed' THEN 1 ELSE 0 END as OrderBy
FROM dbo.Nationality
ORDER BY OrderBy, Nationality
If you want to remove this from the result set you can use a derived table.
SELECT NationalityID, Nationality
FROM
( SELECT DISTINCT NationalityID, Nationality, CASE WHEN Nationality =
'not disclosed' THEN 1 ELSE 0 END as OrderBy
FROM dbo.Nationality ) A
ORDER BY OrderBy, Nationality
John
"Italian Pete" wrote:

> I have a list of Nationalities which I want to sort alphabetically except
> for the value of Nationality which is "not disclosed" which I would like t
o
> put at the top of the list.
> I'm trying to do this with the following query but there seems to be a
> problem with putting the ORDER BY in front of the UNION keyword
> :
> SELECT TOP 100 PERCENT NationalityID, Nationality
> FROM dbo.Nationality
> WHERE (Nationality <>'not disclosed')
> ORDER BY Nationality
> UNION
> SELECT TOP 100 PERCENT NationalityID, Nationality
> FROM dbo.Nationality
> WHERE (Nationality = 'not disclosed')
> I tried using brackets around the first part of the query but that didn't
> work.
> Any help much appreciated.
> Pete|||Perfect!! Works a treat.
Thanks John
"John Bell" wrote:
> Hi
> You can add an extra columns (examples are not tested!)
> SELECT TOP 100 PERCENT NationalityID, Nationality, 0 as OrderBy
> FROM dbo.Nationality
> WHERE Nationality <>'not disclosed'
> UNION
> SELECT TOP 100 PERCENT NationalityID, Nationality, 1
> FROM dbo.Nationality
> WHERE Nationality = 'not disclosed'
> ORDER BY OrderBy, Nationality
> Although you do not need a UNION in this example:
> SELECT DISTINCT NationalityID, Nationality, CASE WHEN Nationality = 'n
ot
> disclosed' THEN 1 ELSE 0 END as OrderBy
> FROM dbo.Nationality
> ORDER BY OrderBy, Nationality
> If you want to remove this from the result set you can use a derived table
.
> SELECT NationalityID, Nationality
> FROM
> ( SELECT DISTINCT NationalityID, Nationality, CASE WHEN Nationality =
> 'not disclosed' THEN 1 ELSE 0 END as OrderBy
> FROM dbo.Nationality ) A
> ORDER BY OrderBy, Nationality
> John
> "Italian Pete" wrote:
>|||Italian wrote on Fri, 27 May 2005 04:59:53 -0700:
> Perfect!! Works a treat.
> Thanks John
> "John Bell" wrote:
>
Rather than using a UNION, you can do this in a single SELECT which should
be more efficient:
SELECT TOP 100 PERCENT NationalityID, Nationality
FROM dbo.Nationality
ORDER BY CASE WHEN Nationality = 'not disclosed' THEN 0 ELSE 1 END
Dan|||Daniel wrote to Italian Pete on Fri, 27 May 2005 15:25:34 +0100:

> Italian wrote on Fri, 27 May 2005 04:59:53 -0700:
>
> Rather than using a UNION, you can do this in a single SELECT which should
> be more efficient:
> SELECT TOP 100 PERCENT NationalityID, Nationality
> FROM dbo.Nationality
> ORDER BY CASE WHEN Nationality = 'not disclosed' THEN 0 ELSE 1 END
>
Just noticed that's almost the same as Italian Pete posted. However, this
gives you just the 2 columns you wanted and doesn't require a derived table.
Dan|||Daniel wrote to Daniel Crichton on Fri, 27 May 2005 15:39:22 +0100:

> Just noticed that's almost the same as Italian Pete posted. However, this
> gives you just the 2 columns you wanted and doesn't require a derived
> table.
You know what, I need more caffeine and sleep. I meant John Bell.
:\
Dan