Get latest date after joining with another table and grouping by some other column mysql-CodePudding

I have 2 tables like this:

table_1 :

table_2 :

I want to join these two tables and get the latest rank of all the ids.

I got upto this query:

SELECT a.Name, b.Rank
FROM table_1 a
INNER JOIN table_2 b
ON a.id = b.id
GROUP BY id

This gives me the rank based for each id, but not the rank on the latest date.

I'm not sure how to do this. Any help is appreciated.

Please note that I have 100s of different dates for same user ids in table_2, and when joined with table_1, should result in latest rank. This is my requirement.

CodePudding user response：

I suspect that you are using MySQL 5.7 or earlier, so we can use a join approach here:

SELECT a.Name, b1.Rank
FROM table_1 a
INNER JOIN table_2 b1 ON b1.id = a.id
INNER JOIN
(
    SELECT id, MAX(Date) AS MaxDate
    FROM table_2
    GROUP BY id
) b2
    ON b2.id = b1.id AND b2.MaxDate = b1.Date;

Note that from MySQL version 8 and onwards RANK is a reserved MySQL keyword and would need to be escaped in backticks or double quotes in order to use it as an alias. But, on 5.7 or earlier, the above code should work as is.