How to select the id with max value that in another table

Question

Here are 2 tables.

Table 1
id  value
1   3
2   2
3   3
4   1
5   4
6   3

Table 2
id
1
3
4

How do I get the ids that are in Table 2 which have the max value in Table 1?

Output:

id
1
3

I already tried the following to get the max value, but I cannot figure out how to use it in a single query to get the matching rows. Because I think I need to select from the same table I just inner joined.

select max(table1.value) 
from table2 
inner join table1 on table1.id = table2.id;

First join the two tables, then get the rows with the maximum value from that result. — Barmar, Feb 25 '20 at 00:33
See https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column?rq=1 for how to do the second step. — Barmar, Feb 25 '20 at 00:34
I already tried `select max(table1.value) from table2 inner join table1 on table1.id = table2.id;` to get the max value, I can't figure out how to reuse it to get rows. — user6456568, Feb 25 '20 at 00:48
Show what you tried. We'll help you fix it, we won't write it for you. — Barmar, Feb 25 '20 at 00:49

score 4 · Answer 1 · answered Feb 25 '20 at 00:55

Here is one method:

select t2.id
from (select t2.*, rank() over (order by value desc) as seqnum
      from table2 t2 join
           table1 t1
           on t2.id = t1.id
     ) t
where seqnum = 1;

Or, an alternative that puts all the ids on one row:

select group_concat(t2.id) as ids
from table2 t2 join
     table1 t1
     on t2.id = t1.id
group by t1.value
order by t1.value desc
limit 1;

Nick · Accepted Answer · 2020-02-25T03:07:44.130

You have a couple of options available without using window functions:

You can use a WHERE clause to select only id values that have a value equal to the MAX(value) from your query and an id that is in Table2:

    SELECT t1.id
    FROM Table1 t1
    WHERE value = (
      SELECT MAX(t1.value)
      FROM Table2 t2
      JOIN Table1 t1 ON t1.id = t2.id
    )
    AND id IN (SELECT id FROM Table2)

You can JOIN your query to Table1 and Table2 again, matching the value in Table1 and the id in Table2:

    SELECT t1.id
    FROM (
      SELECT MAX(t1.value) AS max_value
      FROM Table2 t2
      JOIN Table1 t1 ON t1.id = t2.id
    ) t
    JOIN Table1 t1 ON t1.value = t.max_value
    JOIN Table2 t2 ON t2.id = t1.id

In both cases the output is

id
1
3

Demo on SQLFiddle

What if adding row (6,3) in Table 1? Your method will return 6 as well but 6 is not in Table 2 — user6456568, Feb 25 '20 at 03:02

score 0 · Answer 3 · answered Feb 25 '20 at 03:57

0

Too low to comment but from the SQL statement you gave, you just need to add the tableid in your select parameters.

select table2.id, max(table1.value) 
from table2 
inner join table1 on table1.id = table2.id;

answered Feb 25 '20 at 03:57

R. Castro

1

How to select the id with max value that in another table

3 Answers3