Search code examples
kdb

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

Solution

  • 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"