CODEWITHSUNDEEP
kdb+
duckman
duckman

Reputation: 747

KDB: how to get distinct values of rows in a table?

So I have a tables with number of rows that contain some values. I would like to get the unique values, not the unique rows.

columnA
"AZ;B;C"
"AT;B;D"
"E;F"
"C;D"
"A;D"
"A"

So this is my table. I want to get a list of : A;AT;AZ;B;C;D;E;F. I tried select distinct from flip indicators which just turns this into a very long list of the rows

Upvotes: 0

Views: 1560

Answers (2)

Maurice Lim
Maurice Lim

Reputation: 883

You could try this:

q)tbl:([] columnA:("AZ;B;C";"AT;B;C";"A;B;D";"E;F";"C;D";"A;D";enlist"A"))
q)tbl
columnA
--------
"AZ;B;C"
"AT;B;C"
"A;B;D"
"E;F"
"C;D"
"A;D"
,"A"
q)";"sv asc distinct exec";"vs";"sv columnA from tbl
"A;AT;AZ;B;C;D;E;F"

If the last row of your table is an atom, then you could try this:

q)tbl:([] columnA:("AZ;B;C";"AT;B;C";"A;B;D";"E;F";"C;D";"A;D";"A"))
q)tbl
columnA
--------
"AZ;B;C"
"AT;B;C"
"A;B;D"
"E;F"
"C;D"
"A;D"
"A"
q)exec ";"sv asc distinct ";"vs -1_raze{x,";"}each columnA from tbl
"A;AT;AZ;B;C;D;E;F"

Upvotes: 4

S.Stewart
S.Stewart

Reputation: 611

This is what you could do:

q)t:([]a:`a`b`c;b:`a`f`g;c:`f`e`a)
q)t
a b c
-----
a a f
b f e
c g a

q)distinct raze value flip t
`a`b`c`f`g`e

Upvotes: 2

Related Questions