Delete rows based on condition

[sifatnabil]

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.

[tresesco]

Try like:

Tab:

Load

          A,

          B,

          C,

From <>;

Inner Join

Load

          Max(A) as A

Resident Tab Group By B;

[rwunderlich · Accepted solution]

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

http://robwunderlich.com

http://masterssummit.com

[sifatnabil]

Hi,

I've tried this and it worked. Thanks!

[sunilkumarqv]

Hi Sifat,

make it correct and close the thread