CODEWITHSUNDEEP
kdb+
LR2019
LR2019

Reputation: 61

How do I remove row with duplicate value in kdb?

I have a table of data in kdb and I would like to use q to remove the rows which contain a duplicate value in one column.

For example, if I have the following table where there is a duplicate value in the Age column:

Name    Age    Degree
---------------------
Alice   26     Science
Bob     34     Arts
Carrie  26     Engineering

How would I delete the third row so I end up with the following:

Name    Age    Degree
---------------------
Alice   26     Science
Bob     34     Arts

Thanks!

Upvotes: 1

Views: 1029

Answers (2)

Andrew McNaught
Andrew McNaught

Reputation: 31

You can delete any of the duplicates in any columns using this:

q)delete from t where ({not x in 1#x};i) fby Age
Name  Age Degree
-----------------
Alice 26  Science
Bob   34  Arts

Could also be solved using a by clause instead of fby, but in this case to get the first occurrence of each age you have to use reverse

q)0!select by Age from reverse t
Age Name  Degree
-----------------
26  Alice Science
34  Bob   Arts

Upvotes: 3

terrylynch
terrylynch

Reputation: 13572

You could do

select from t where i=(first;i)fby Age

Upvotes: 5

Related Questions