Re: NULLS FIRST option in CREATE INDEX?

From: Alanoly J. Andrews <alanolya_at_invera.com>
Date: 9 Jun 2003 05:30:14 -0700
Message-ID: <e76061b9.0306090430.5a7ff0b0@posting.google.com>

"Maximus" <qweqwe_at_qwqwewq.com> wrote in message news:<L26Ea.142907$3C2.4666777_at_news3.calgary.shaw.ca>...
>
> Why not just UNION ALL two selects together? This example will give you
> nulls first without doing a sort:
>
> select * from table where key_expr is null
> union all
> select * from table where key_expr is not null

Thanks...but such a union will not give me the desired order of rows. Consider the following rows:

          col1        col2
          ----        ----
           A          NULL
           B          NULL
           A          abc
           B          abc

The order produced by your SQL will be the same as the above. But the order desired by my SQL ("order by col1, col2 nulls first") as described earlier is:

           A          NULL
           A          abc
           B          NULL
           B          abc

Alanoly. Received on Mon Jun 09 2003 - 07:30:14 CDT