CODEWITHSUNDEEP
mysqlsql
user6456568
user6456568

Reputation: 599

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

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;

Upvotes: 0

Views: 3823

Answers (3)

R. Castro
R. Castro

Reputation: 1

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;

Upvotes: 0

Nick
Nick

Reputation: 147146

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

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269483

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;

Upvotes: 4

Related Questions