tags:

views:

34

answers:

2

Hi,

I have a requirement.

I have two tables say TableA and TableB. Both having a column called "rec_id". My requirement is to get the maximum of the values contained in these two columns using a single query.

eg: In TableA, I have "rec_id" values as {1,5,6} and in TableB, I have "rec_id" values as {1,4,2}. So after executing the query, I want "6" as the result since 6 is the maximum value from these two columns, out of these two tables.

Thanks in Advance, Anish Kurian

+4  A: 
select max(rec_id) from 
(
  (select rec_id from tablea)
 union all
  (select rec_id from tableb)
) combined
Nathan Feger
+1 You beat me by 12 sec! :)
Bill Karwin
@Bill Karwin sometimes its about the luck over skill. Thanks
Nathan Feger
A: 
select max(rec_id) from 
(
  (select MAX(rec_id) AS rec_id from tablea)
 union
  (select MAX(rec_id) AS rec_id from tableb)
) combined

In comparison to Nathan Feger's answer this would be more performant

zerkms
"In comparison to Nathan Feger's answer this would be more performant" - not necessarily; it depends on how the DB engine handles it. I would actually expect it to perform slightly worse, as you still have to scan through all rec_id values in both tables, but your query has to derive three maximum values whereas Nathan's only has to derive one.
Mark Bannister
With index covered `rec_id` there will be no scans. The max values are taken with O(1) complexity.
zerkms
You would still have to read through the indexes.
Mark Bannister
Nope. Since indexes in mysql is B-Tree, which is sorted structure, mysql optimizer can take the max value using only one read operation. So, for 2 indexes it will be 2 read operations and very fast comparison of 2 ints. And in checked answer it will be fullscan. Create 2 tables with 10M records each and check it.
zerkms