Take the 2-minute tour ×

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

share|improve this question
add comment

2 Answers

up vote 6 down vote accepted
select max(rec_id) from 
  (select rec_id from tablea)
 union all
  (select rec_id from tableb)
) combined
share|improve this answer
+1 You beat me by 12 sec! :) –  Bill Karwin Oct 1 '10 at 0:02
@Bill Karwin sometimes its about the luck over skill. Thanks –  Nathan Feger Oct 1 '10 at 0:12
add comment
select max(rec_id) from 
  (select MAX(rec_id) AS rec_id from tablea)
  (select MAX(rec_id) AS rec_id from tableb)
) combined

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

share|improve this answer
"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 Oct 1 '10 at 13:24
With index covered rec_id there will be no scans. The max values are taken with O(1) complexity. –  zerkms Oct 2 '10 at 4:28
You would still have to read through the indexes. –  Mark Bannister Oct 4 '10 at 12:52
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 Oct 5 '10 at 0:40
add comment

Your Answer


