CODEWITHSUNDEEP
sql
mikizas
mikizas

Reputation: 341

SQL - Summarize column with maximum date value and other fields

I have a table with the following fields:

Id|Date|Name
---------------
A|2019-04-24|"VALUE1"
A|2019-04-23|"VALUE2"
A|2019-06-11|"VALUE3"
A|2019-06-12|"VALUE4"
B|2019-05-21|"VALUE5"
B|2019-05-22|"VALUE6"
B|2019-03-13|"VALUE7"
C|2019-01-03|"VALUE8"

I would like to get one line per Id having the info of the maximum date line. This would be the output:

Id|Date|Name
---------------
A|2019-06-12|"VALUE4"
B|2019-05-22|"VALUE6"
C|2019-01-03|"VALUE8"

I have achieved through a group by getting the Id and the MAX Date, but not the value associated to that date.

What I am working on now is to inner join that table with the input one joining it on date and id, but I am not able to join on two fields.

Is there any way to bring to the result the value field related to the max date in the group by clause?

Otherwise, How could I join on two different fields those two tables?

Any Suggestion?

Thank you so much!!

Upvotes: 0

Views: 155

Answers (1)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can use a correlated subquery :

select t.*
from table t
where t.date = (select max(t1.date) from table t1 where t1.id = t.id);

However, Most of DBMS supports analytical functions, so you can use :

select t.*
from (select t.*, row_number() over (partition by t.id order by t.date desc) as seq
      from table t
     ) t
where seq = 1;

Upvotes: 2

Related Questions