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

t2

Date	Types	Report_ID	Name
01.01.2020	Type 1	1	Name 1
01.01.2020	Type 1	2	Name 2
01.01.2020	Type 3	3	Name 3

view

Date	Types	Name	Value	Report_ID
01.01.2020	Type 1	Name 2	Value 2	2
01.01.2020	Type 3	Name 3	Value 3	3

Please, [**do not post images of data**](https://meta.stackoverflow.com/q/285551). You can check [SQL tag wiki](https://stackoverflow.com/tags/sql/info) to check how to create an answerable SQL question. — astentx, Feb 04 '21 at 12:19
@MoeAmine You don't need date column in t1 for reference since you have report_id in both the tables. — Kazi Mohammad Ali Nur Romel, Feb 08 '21 at 14:13

forpas · Accepted Answer · 2021-02-07T12:03:01.177

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

score 1 · Answer 2 · answered Feb 07 '21 at 20:51

1

Using ROW_NUMBER():

WITH cte AS (
  SELECT t2.*, t1.Value, 
         ROW_NUMBER() OVER(PARTITION BY `Date`, Types ORDER BY Report_ID DESC) AS rn
  FROM t2
  JOIN t1 ON t1.Report_ID = t2.Report_ID
)
SELECT * FROM cte WHERE rn = 1;

db<>fiddle demo

answered Feb 07 '21 at 20:51

Lukasz Szozda

162,964
23
234
275

Gordon Linoff · Answer 3 · 2021-02-04T14:40:11.007

0

This answers the original version of the question.

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

The reference table is not needed for this. Your logic should do what you want with t2 in the subquery:

select t2.*
from t2
where t2.Report_ID = (select max(tt2.Report_ID)
                      from t2 tt2
                      where tt2.Date = t2.date and tt2.Type = t2.Type
                     );

edited Feb 04 '21 at 14:40

answered Feb 04 '21 at 12:19

Gordon Linoff

1,242,037
58
646
786

Sorry, I added by mistake Type in t1. It does not have it in fact. – MoeAmine Feb 04 '21 at 12:31

score 0 · Answer 4 · answered Feb 04 '21 at 12:20

0

You can use NOT EXISTS as follows:

select t2.*
  from t2 
  --join t1 on t1.Report_ID = t2.Report_ID -- use it if you want data from t1 in SELECT 
where not exists 
      (select 1 from t2 t22 
        where t22.date = t2.date and t22.type = t2.type 
          and t22.Report_ID > t2.Report_ID)

answered Feb 04 '21 at 12:20

Popeye

35,427
4
10
31

Sorry, I added by mistake Type in t1. It does not have it in fact. – MoeAmine Feb 04 '21 at 12:31

Kazi Mohammad Ali Nur Romel · Answer 5 · 2021-02-08T14:41:42.157

You can easily achieve that through row_number() and CTE. First we need to join t1 and t2 to get the value column from t1. We used row_number() to put a sequence number in every row starting from highest Report_ID to lowest for a particular type in a given date. Then we only consider the rows with lowest sequence number which represents highest report_id for any particular type of a given da.

With cte as
  (
select t2.date,t2.types,t2.report_id,t2.name ,t1.value ,row_number () over (partition by date,types order by t2.report_id desc) RowNumber 
    from t2 inner join  t1 on t2.report_id=t1.report_id 
  )
  select  date_format(date,"%Y.%m.%d") date,types,name,value,report_id from cte where RowNumber=1

Output:

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

5 Answers5