ORDER BY before UNION syntax error

27 May 2005 11:08 AM

Italian Pete

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.

Pete

27 May 2005 11:18 AM

John Bell

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 = '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

Show quoteHide quote

"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 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.
>
> Pete

27 May 2005 11:59 AM

Italian Pete

Perfect!! Works a treat.
Thanks John

Show quoteHide quote

"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 = '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 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.
> >
> > Pete

27 May 2005 2:25 PM

Daniel Crichton

Italian wrote on Fri, 27 May 2005 04:59:53 -0700:

Show quoteHide quote

> 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 =
>> '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 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.
>>>
>>> Pete

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

27 May 2005 2:39 PM

Daniel Crichton

Daniel wrote to Italian Pete on Fri, 27 May 2005 15:25:34 +0100:

Show quoteHide quote

> Italian wrote on Fri, 27 May 2005 04:59:53 -0700:
>
>> 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 =
>>> '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 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.
>>>>
>>>> Pete
>
> 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

27 May 2005 2:44 PM

Daniel Crichton

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