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.
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)...
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