Load highest value for Key

[Anonymous]

Hello,

I think there must be a pretty simple solution but I just cant figure it out.

I got a key, which appears multiple times in one table with several time-stamps NOT sorted.

What I want is a table with each key appearing once, distinct, and the latest time-stamp to it from table A.

Table A:

Key, Date, Time

10241, 20160204, 94525

10241, 20160204, 83858

10241, 20160204, 101245

10241, 20160204, 73248

10241, 20160204, 165847

10241, 20160204, 124151

10247, 20160204, 45712

10247, 20160204, 54623

10247, 20160204, 112154

10247, 20160204, 73248

10258, 20160204, 73248

Any input is appreciated!!

Thx,

Lucas

[Anonymous]

found the answer myself

Check which value is the Min and Max in Load Script

[sunny_talwar · Accepted solution]

May be this:

[Table A]:

LOAD * Inline [

Key, Date, Time

10241, 20160204, 94525

10241, 20160204, 83858

10241, 20160204, 101245

10241, 20160204, 73248

10241, 20160204, 165847

10241, 20160204, 124151

10247, 20160204, 45712

10247, 20160204, 54623

10247, 20160204, 112154

10247, 20160204, 73248

10258, 20160204, 73248

];

Right Join ([Table A])

LOAD Key,

  Date,

  Max(Time) as Time

Resident [Table A]

Group By Key, Date;

[Gysbert_Wassenaar]

Use the Max function:

LOAD

     Key, Date, Max(Time) as Time

FROM

     MySource

GROUP BY

     Key, Date

     ;