Re: Order by with case unsensitive in UNION

From: <toneczar_at_erols.com>
Date: Mon, 04 Jan 1999 14:11:23 GMT
Message-ID: <76qi6b$s7o$1@nnrp1.dejanews.com>

Xavier.Arques_at_alcatel.fr wrote:

> I'm trying to sort datas in an UNION with case unsensitive:
>
> SELECT address1 from A
> UNION
> SELECT address2 from B
> ORDER BY LOWER(1)
>
> but it doesn't work.
>
> Can some one tell me how to do ?

In SQL*Plus, you could add a "hidden" column like this, and use it to sort:

SQL> column sortkey noprint
SQL> select lower(address1) sortkey, address1 address

     from   a
     union
     select lower(address2) sortkey, address2 address
     from   b
     order by 1;

If not in SQL*Plus, you could select the data as above and just not display that sortkey column on screen, however your application does that.

Chris

---

Chris Hamilton -- toneczar_at_erols.com
http://www.serve.com/cowpb/chamilton.html City of Washington Pipe Band

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Mon Jan 04 1999 - 08:11:23 CST