LOAD the latest record with duplicate LastUpdatedDates

[haymarketpaul]

QV12 SR3

Wondering the best way to LOAD the latest record when my dataset has this sort of thing...

ID	Email	Last Update Date
1	me@somewhere.com	10/06/2016
2	me@somewhere.com	22/03/2016
3	me@somewhere.com	10/06/2016
4	me@somewhere.com	03/01/2016

I need the latest record based on [Last Update Date] and then if they are equal (ID 1 & 3 above for example) then the highest ID value (ID is an autonumber) - which would leave me with just one record per email address.

Is it a case of preceding FirstSortedValues (if that's possible?) or is there a better way ?

Any pointers appreciated

[haymarketpaul]

Thanks - saw it in another post you replied to

[arnauayuso]

Hi, thank you for the information, it is very useful. But I have a problem when using it in my script.

I have more dimensions.

If I put them in the Group by Clause, the duplicates appear againg, because there are dimensions with different values.

If i do not put them, Invalid expesion apears as an error.

Could you please help me with that?

I attach an example:

Table:

LOAD Email,

     Number,

     FirstSortedValue(ID, -([Last Update Date]*1000+ID)) as ID,

    FirstSortedValue([Last Update Date], -([Last Update Date]*1000+ID)) as [Last Update Date]

Group By Email, Number;

LOAD * INLINE [
ID, Email, Number, Last Update Date
1, me@somewhere.com, 673, 10/06/2016
2, me@somewhere.com, 619, 22/03/2016
3, me@somewhere.com, 619, 10/06/2016
4, me@somewhere.com, 619, 03/01/2016
];

Result:

1, me@somewhere.com, 673, 10/06/2016

3, me@somewhere.com, 619, 10/06/2016