Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
In the load script, I have a table with several columns, but want to work with 2 columns, "Version", and "ID".
The same ID values populate with different versions; for example: ID=1234 resides in 3 rows, with Version=1, 2, and 3. I wish to delete the rows with the lower versions and keep the row with the latest version for each ID.
This would require finding the maximum value of "Version" for that particular ID, and then deleting the entire row. How could I achieve this in the load script?
Thank you.
Like this:
data:
LOAD * INLINE [
ID, Version, Value
1, 1, 100
1, 2, 200
2, 1, 300
3, 3, 150
3, 4, 200
]
;
INNER JOIN (data)
LOAD
ID,
max(Version) as Version
RESIDENT data
GROUP BY ID
;
-Rob
Try like:
Tab:
Load
A,
B,
C,
From <>;
Inner Join
Load
Max(A) as A
Resident Tab Group By B;
Like this:
data:
LOAD * INLINE [
ID, Version, Value
1, 1, 100
1, 2, 200
2, 1, 300
3, 3, 150
3, 4, 200
]
;
INNER JOIN (data)
LOAD
ID,
max(Version) as Version
RESIDENT data
GROUP BY ID
;
-Rob
Hi,
I've tried this and it worked. Thanks!
Hi Sifat,
make it correct and close the thread