mysql - Select all rows where maximum value on a one column from two tables with union

Question

I got two tables with identical structure. From those tables I need to get rows with highest value on rate column where fix_id is the same.

Table1

fix_id | rate  |  proc  | unique_id
 2     |  72   |   50   | 23_tab1
 3     |  98   |   70   | 24_tab1
 4     |  78   |   80   | 25_tab1

table2

fix_id | rate  |  proc  | unique_id
 2     |  75   |   999  | 23_tab2
 3     |  80   |   179  | 24_tab2
 4     |  82   |   898  | 25_tab2

Expected result

fix_id | rate  |  proc  | unique_id
 2     |  75   |   999  | 23_tab2
 3     |  98   |   70   | 24_tab1
 4     |  82   |   898  | 25_tab2

I've tried this...

Select fix_id,proc,unique_id,MAX(rate) rate from 
(Select fix_id,proc,unique_id,MAX(rate) rate from table1 group by fix_id
UNION ALL SELECT fix_id,proc,unique_id,MAX(rate) rate from table2 group by fix_id ) group by fix_id

I get the highest values from rate column but the values from other columns are incorrect.

question from:https://stackoverflow.com/questions/65598258/select-all-rows-where-maximum-value-on-a-one-column-from-two-tables-with-union

Answer 1

As fix_id is unique in both tables, the answer with CASE statements (https://stackoverflow.com/a/65609931/53341) is likely the fastest (so, I've upvoted that)...

• Join once
• Compare rates, on each row
• Pick which table to read from, on each row

For large numbers of columns, however, it's unwieldy to type all the CASE statements. So, here is a shorter version, though it probably takes twice as long to run...

SELECT t1.*
  FROM table1 AS t1 INNER JOIN table2 AS t2 ON t1.fix_id = t2.fix_id
 WHERE t1.rate >= t2.rate

UNION ALL

SELECT t2.*
  FROM table1 AS t1 INNER JOIN table2 AS t2 ON t1.fix_id = t2.fix_id
 WHERE t1.rate <  t2.rate