cancel
Showing results for
Did you mean:

## idistinct

New Contributor
q)show t:([]a:1 1 1;b:1 2 2;c:`a`b`c)
a b c
-----
1 1 a
1 2 b
1 2 c

i can find distinct rows considering a,b by

q)distinct select a,b from t
a b
---
1 1
1 2

but need to incorporate the c column so the result i want might be

a b c
------
1 1 a
1 2 b

so an idistinct would be helpful à la iasc:

q)t idistinct select a,b from t
a b c
------
1 1 a
1 2 b

can idistinct or other useful function be built with similar performance to distinct?

thanks guys.
3 REPLIES 3
New Contributor II
I haven't profiled the performance but have you considered fby?

q)select from t where i=(first;i)fby ([]a;b)

a b c

-----

1 1 a

1 2 b

Terry

New Contributor
you can implement idistinct with the find operator

q)idistinct:{x?distinct x}

q)t idistinct select a,b from t
a b c
-----
1 1 a
1 2 b

Abdul
Contributor
If you only want distinct value and not necessarily the first entry, you can use

select by a,b from t

This will return last row for each a,b distinct combination.

Or if you want first entry, you can use:

select first c by a,b from t

This gives some performance improvement over fby but if you have fat table and require many columns into output then fby makes that job easy.

On Wednesday, 20 June 2018 14:54:15 UTC+1, effbiae wrote:
q)show t:([]a:1 1 1;b:1 2 2;c:`a`b`c)
a b c
-----
1 1 a
1 2 b
1 2 c

i can find distinct rows considering a,b by

q)distinct select a,b from t
a b
---
1 1
1 2

but need to incorporate the c column so the result i want might be

a b c
------
1 1 a
1 2 b

so an idistinct would be helpful à la iasc:

q)t idistinct select a,b from t
a b c
------
1 1 a
1 2 b

can idistinct or other useful function be built with similar performance to distinct?

thanks guys.