mysql - Get max ID for every Type and every Date from a lookup table

Question

I want to keep the highest report id (Report_ID) for every type (Types) for every single date (Date)

Note: The data column has multiple dates, only 01.01.2021 is shown below.

Question: t1 is the lookup table that I need to use and my challenge is that it does not contain a date column for reference.

select t2.*
from t2
where t1.Report_ID = (select max(t1.Report_ID)
                     from t1
                     where t2.Date = ??? and t2.Types = ???
                    );

t1

Report_ID	Name	Value
1	Name 1	Value 1
2	Name 2	Value 2
3	Name 3	Value 3

question from:https://stackoverflow.com/questions/66045431/get-max-id-for-every-type-and-every-date-from-a-lookup-table

Answer 1

With this query:

SELECT Date, Types, MAX(Report_ID) Report_ID
FROM t2
GROUP BY Date, Types

you get the max Report_ID for each Date and Types

Join it to t1:

SELECT t2.Date, t2.Types, t1.Name, t1.Value, t1.Report_ID
FROM t1 
INNER JOIN (
  SELECT Date, Types, MAX(Report_ID) Report_ID
  FROM t2
  GROUP BY Date, Types
) t2 ON t2.Report_ID = t1.Report_ID

See the demo.
Results:

Date	Types	Name	Value	Report_ID
2020-01-01	Type 1	Name 2	Value 2	2
2020-01-01	Type 3	Name 3	Value 3	3