Re: MIN value from a column in 2 tables.

From: Matthias Rogel <rogelREMOVE_at_THISweb.de>
Date: Tue, 21 Jan 2003 10:24:00 +0100
Message-ID: <b0j3jg$q02qo$1@ID-86071.news.dfncis.de>

Thank you Billy for this improvement

matthias

Billy Verreynne wrote:
> Mark wrote:
>
>

>>>select * from
>>>(
>>>select min(ID) as ID, 'table1' as this_table from table1
>>>union all
>>>select min(ID) as ID, 'table2' as this_table from table2
>>>)
>>>where ID =
>>>select min(ID) from (select * from table1 union select * from table2);
>>

>
> Just note that table1 and table2 are scanned twice - this will have serious
> performance implications when dealing with larger tables.
>
> I would rather do it like this:
>
> SELECT
> *
> FROM (
> SELECT
> *
> FROM
> ( select min(ID) as ID, 'table1' as this_table from table1
> union all
> select min(ID) as ID, 'table2' as this_table from table2 )
> ORDER BY id DESC
> )
> WHERE rownum = 1
>
> This way, tables 1 and 2 are only hit once.
>
> --
> Billy
Received on Tue Jan 21 2003 - 03:24:00 CST