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.

I’m guessing you’re using MySQL? If column are selected without group by them or an aggregation function applied on them, you won’t be the one who decides which of them will be selected. — sagi, Jan 06 '21 at 15:10

Akash Sahani · Answer 1 · 2021-01-07T09:50:04.627

It can be done using CASE statement. Try this query

select 
(case 
   when T1.rate > T2.rate then T1.fix_id else T2.fix_id 
end) as fix_id, 

(case 
   when T1.rate > T2.rate then T1.rate else T2.rate
end) as rate, 

(case 
   when T1.rate > T2.rate then T1.proc else T2.proc 
end) as proc,

(case 
   when T1.rate > T2.rate then T1.unique_id else T2.unique_id 
end) as unique_id

from table1 as T1, table2 as T2 where T1.id = T2.id

score 1 · Answer 2 · answered Jan 06 '21 at 15:11

1

You can use row_number():

select t.*
from (select fix_id, proc, unique_id, rate,
             row_number() over (partition by fix_id order by rate desc) as seqnum
      from ((select fix_id, proc, unique_id, rate from table1
            ) union all
            (select fix_id, proc, unique_id, rate from table2
            ) 
           ) t
     ) t
where seqnum = 1;

answered Jan 06 '21 at 15:11

Gordon Linoff

1,242,037
58
646
786

https://stackoverflow.com/questions/8387587/selecting-a-record-with-max-value/8387611#8387611 Then you need something like this. – sagi Jan 06 '21 at 15:17
I've tried this but I get the error ....for the right syntax to use near '(partition by fix_id order by rate desc) as seqnum........ – user2741313 Jan 06 '21 at 15:21
@user2741313 . . . MySQL has supported window functions since Version 8 was released a few years ago. – Gordon Linoff Jan 06 '21 at 15:55
I see, is there anyway to write the query to work on the previous versions, I'm on share server and I'm not able to make upgrade – user2741313 Jan 06 '21 at 15:57

score 0 · Accepted Answer · answered Jan 07 '21 at 16:42

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

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

3 Answers3