Fetched latest date rows from table

[pratham39]

Hi Everyone,

I have below table in my database,

Green colors records are final records according to latest date against each ID ,

So basically i want to fetch only latest record for each id ,

so my final output should be,

I want to achieve this in backend data load editor,

can you guys help me to achieve above output..

Thanks

[Mark_Little · Accepted solution]

HI,

TABLE

Load 

ID,

MAX(Timestamp) As Timestamp

FROM ..

GROUPBY ID;

LEFT JOIN

LOAD

ID,

City,

Status,

Timestamp

From ...;

[MayilVahanan · Accepted solution]

Hi 

Try like below

SET DateFormat='M/D/YYYY';

Table1:

Load ID, City, Status, Timestamp from yourtable;

Inner join(Table1)

Load ID, Date(Max(Timestamp)) as Timestamp resident Table1 group by ID;

[DiegoF]

Hi, I have a code like yours to get the last date, but I do use a left join table, whats the difference exactly? I mean, i know what left and inner do separate joins, left joining only the origin table values and inner only when they are both the same, more or less, but I don´t know if my Left Join it´s better to be changed by an inner join.

Thanks!

[pratham39]

in above scenario both will work in same way!

but if you have large dataset then please check both scenarios and whichever will be fast do that

Thanks

[pratham39]

Thanks @Mark_Little @MayilVahanan !

[MayilVahanan]

Hi

It depends on scenario, 

For ex: if you want to create a flag for maximum timestamp, instead of reducing the remaining dates, then go with left join and create a flag when MaxDate and Date are same.