Max Max - 3 months ago 7
MySQL Question

SQL select MAX from other table

I have two tables.

table1:



| ID | NAME |
|----|------|
| 1 | aaa |
| 2 | aaa |
| 3 | aaa |
| 4 | bbb |
| 5 | bbb |


table2:



| ID | DATE |
|----|----------|
| 1 | 12/07/10 |
| 2 | 12/07/13 |
| 3 | 12/07/16 |
| 4 | 12/07/08 |
| 5 | 12/07/20 |


Help me pls, I don't know how to
SELECT MAX ID
in
table1
by
date
in
table2
.

For example result should be:


for "aaa": ID 3 from table2

for "bbb": ID 5 from table2


I'm trying something like that:

DATE = (SELECT MAX(DATE) FROM table2 t2, table1 t1 WHERE t1.NAME = "aaa")


But it's not working... Have you got some idea?

Answer

You need to add an additional condition (t1.id = t2.id) for the join:

SELECT MAX(DATE) 
FROM table2 t2, table1 t1 
WHERE t1.NAME = "aaa"
  AND t1.id = t2.id

But please - don't join with comma - use explicit JOIN syntax instead:

SELECT MAX(DATE) 
FROM table2 t2
JOIN table1 t1 
  ON t1.id = t2.id
WHERE t1.NAME = "aaa"

You can also get all max dates for all names at once using GROUP BY name:

SELECT t1.NAME, MAX(t2.DATE) 
FROM table2 t2
JOIN table1 t1 
  ON t1.id = t2.id
GROUP BY t1.NAME
Comments