Re: MIN value from a column in 2 tables.

From: Billy Verreynne <vslabs_at_onwe.co.za>
Date: Tue, 21 Jan 2003 11:22:04 +0000
Message-ID: <b0j3ft$o9s$1@ctb-nnrp2.saix.net>

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 - 05:22:04 CST