"Thomas Scheiderich" <tfs@deltanet.com> wrote in message
news:407A317C.5030303@deltanet.com...
>I am trying to use a union and get the first select statement to show
>before the second select.
>
> In the following I have only 1 row. It consists of one word "ALL" that I
> want to show first as it is going into a web drop down control. The
> problem is that in Sql Server 6.5, it puts the "ALL" insided the second
> select in alpha order. In Sql Server 7, it puts it at the end. Why do
> they act differently and how can I force the 1st select to show first.
>
> ****************************************************************
> select 'ALL' as carrier,'ALL' as carrierCode union select
> name,carrier_code from carrier where default_class is not null
> *****************************************************************
>
> Thanks,
>
> Tom.
>

Thomas Scheiderich wrote:
>
> I am trying to use a union and get the first select statement to show
> before the second select.
>
> In the following I have only 1 row. It consists of one word "ALL" that
> I want to show first as it is going into a web drop down control. The
> problem is that in Sql Server 6.5, it puts the "ALL" insided the second
> select in alpha order. In Sql Server 7, it puts it at the end. Why do
> they act differently and how can I force the 1st select to show first.
>
> ****************************************************************
> select 'ALL' as carrier,'ALL' as carrierCode union select
> name,carrier_code from carrier where default_class is not null
> *****************************************************************
>
> Thanks,
>
> Tom.

Anith Sen wrote:

> Aaron,
>
> Since carrier is just an alias for the column, the statement will not
> compile using CASE having a reused alias. One workaround is to make the
> whole UNION query as derived table construct & apply the ORDER BY on the
> outer query.
>
> SELECT *
> FROM ( < union query > ) T
> ORDER BY CASE WHEN carrier = 'ALL' THEN 1 ELSE 2 END ;
>

>