Re: Order by with case unsensitive in UNION

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 04 Jan 1999 14:37:06 GMT
Message-ID: <3694d1b7.4201431@192.86.155.100>

A copy of this was sent to Xavier Arques <Xavier.Arques_at_alcatel.fr> (if that email address didn't require changing) On Mon, 04 Jan 1999 11:59:53 +0100, you 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 ?
>

Here is one way to do it:

select * from
(
select address1 ADDR from a
union
select address2 ADDR from b
)
order by lower( ADDR )
/

btw: if A and B are to be concatenated together (eg: you want every record in A and every record in B) you might want to use UNION ALL instead of UNION as UNION is really:

A + (B-A)

not A+B. Also, a union will general be much slower then a UNION ALL (although with a sort on a function, its not going to be blindingly fast as it is....)  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Mon Jan 04 1999 - 08:37:06 CST