0

I have the following table below and would like to delete all rows that are duplicates. I have created a column dup which counts the number of duplicates.

delete from table where dup>1 would delete all entries of the duplicate and I would still like 1 entry to remain in my table.

ISIN         RIC      BLOOMBERG dup
-----------------------------------
LU1681046006 CWEm.BS  CWEM EB   2
LU1681046006 CWEm.BS  CWE EB    2
LU1681046006 CWEm.BS  CWEM EB   2
LU1681046006 CWEm.BS  CWE EB    2
LU1681046006 CWEm.CHI CWEM IX   2
LU1681046006 CWEm.CHI CWE IX    2
LU1681046006 CWEm.CHI CWEM IX   2
LU1681046006 CWEm.CHI CWE IX    2
LU1681046006 CWE.MI   CWE IM
LU1681046006 WDNR.DE  WDNR GY

So the resulting table should look like:

ISIN         RIC      BLOOMBERG dup
-----------------------------------
LU1681046006 CWEm.BS  CWEM EB   2
LU1681046006 CWEm.BS  CWE EB    2
LU1681046006 CWEm.CHI CWEM IX   2
LU1681046006 CWEm.CHI CWE IX    2
LU1681046006 CWE.MI   CWE IM
LU1681046006 WDNR.DE  WDNR GY

Any idea how I can achieve this?

David Rogers
  • 2,601
  • 4
  • 39
  • 84
TemperingBean
  • 3
  • 2

2 Answers2

7

Try distinct <table name>

This should return you all of the distinct rows of your table

For more info please reference to this link: https://code.kx.com/q/ref/search/#distinct

Hannah Robinson
  • 131
  • 5
4

I think you may be able to use distinct instead of trying to count duplicates.

q)td  
a   b 
----- 
kdj 8 
eeg 1 
nce 9 
jog 5 
cih 4 
hkp 6 
aea 6 
blm 1 
ooe 8 
jgj 5 
jgj 5 

q)distinct td
a   b
-----
kdj 8
eeg 1
nce 9
jog 5
cih 4
hkp 6
aea 6
blm 1
ooe 8
jgj 5
rmorgan
  • 166
  • 5