Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello all,
I would like to retrieve one and only one value for each of the following records, regardless of duplicates.
The criteria is the ID and the minimum date
load * inline [
ID, moddate, activityname, attempt
1, 1/1/2021, ms1, attempt 1
1, 1/1/2021, ms1, attempt 2
1, 1/5/2021, ms2, attempt 3
2, 3/1/2021, ms1, attempt 1
so the results would be
1, 1/1/2021, ms1
2, 3/1/2021, ms2
Hi @RogerG, I suggest you to use a group by clause:
RawData:
load * inline [
ID, moddate, activityname, attempt
1, 1/1/2021, ms1, attempt 1
1, 1/1/2021, ms1, attempt 2
1, 1/5/2021, ms2, attempt 3
2, 3/1/2021, ms1, attempt 1
];
MyData:
load ID,
Min(Date(moddate)) as moddate
Resident RawData
Group by moddate;
drop table RawData;
JG
Thanks, this project was cancelled on me, sorry for the late response