Mark Valids orders from ID-column with latest Date

[AndreasMoller]

Hello

I have a table that looks something like this: 

ID	Order	Date
1	12345	2023-05-15
2	23456	2023-05-14
2	45666	2023-05-13
3	34225	2023-05-14

I want to mark the latest distinct value from ID as valid, so i want the result to look something like this:

ID	Order	Date	Valid
1	12345	2023-05-15	1
2	23456	2023-05-14	1
2	45666	2023-05-13	0
3	34225	2023-05-14	1

Row 3 have the same ID as Row 2 and Row 3 is older then Row 2, thats why i want valid to be 0 on Row 2. 

Can anyone help me?  Thanks!

[Kushal_Chawda · Accepted solution]

@AndreasMoller  try below

Data:
Load ID,
     Order,
     Date
FROM Source;

New:
Load *,
     if(ID=Previous(ID),0,1) as Valid
Resident Data
Order by ID, Date desc;

Drop Table Data;

[AndreasMoller]

That will do it, thank you!