latest row per id

[#1]

Hello all,

Hope everyone is having a good day.

I would like to fetch latest row per id. One id has multiple support contracts associated with them. From these multiple rows I would like to get that row which has highest start date.

Please let me know how can I achieve that.

Any help is appreciated.

Best

Parth

[sunny_talwar]

How does your raw data looks like? can you share few rows of data?

Something along these lines to should work:

Table:

LOAD id,

           [start date],

           ....

FROM ....;

Right Join (Table)

LOAD id,

           Max([start date]) as [start date],

Resident Table

Group By id;

[Anil_Babu_Samineni]

Front end solution may be this

If(Id = Aggr(Max(Rowno(TOTAL)),[start date]), id)

[#4]

Hi Sunny,

Ideally I would like to create a new column called flag which identifies which row is the latest. Please see below how the data looks like:

supp_id	id	type	start date	end date	flag
1	1	gold	12/1/1990	11/30/1995	0
2	1	gold	12/1/1995	11/30/1999	0
3	1	silver	12/1/1999	11/30/2019	1
4	2	silver	8/15/2015	8/14/2016	0
5	2	gold	8/15/2016	8/14/2017	1
6	3	gold	9/1/2015	8/31/2017	1
7	4	silver	12/1/2016	11/30/2018	1
8	5	silver	1/1/2015	12/31/2016	0
9	5	gold	1/1/2016	12/31/2017	1

Please let me know if this is doable.

Thank you

Parth

[sunny_talwar · Accepted solution]

Then try like this:

Table:

LOAD id,

           [start date],

           ....

FROM ....;

Left Join (Table)

LOAD id,

           Max([start date]) as [start date],

           1 as Flag

Resident Table

Group By id;

This will put 1 for max start date. In case you need 0 for other dates, you will have to do a resident load

FinalTable:

LOAD id,

           [start date],

           ....,

           If(Flag = 1, 1, 0) as Flag

Resident Table;

DROP Table Table;

[#6]

Thank you Sunny!!

[#7]

Thank you Anil!!