Choose second largest or smallest number in KDB

Question

Is there a simple way to find the second largest or smallest number in a group of columns of a table?

I can easily find the largest or smallest by using select min/max (a, b, c, d) by i from t.

However I can't seem to figure out a simple way to find the second (or third) largest or smallest from the group.

Thanks

score 5 · Answer 1 · answered Dec 19 '14 at 20:09

5

If you want only second maximum/minimum, you can use:

       q) a: 4 3 5 1 6 8
       q) max a except max a  / second maximum
       q) min a except min a  / second minimum

But if you want general function that would work for any nth min/max, here is one way:

For Nth maximum

       f:a (idesc a)[n-1]   
       q) a (idesc a)[2-1]    // second maximum

For Nth minimum

       f: a (iasc a)[n-1]  
       q) a (iasc a)[2-1]   // second minimum

answered Dec 19 '14 at 20:09

Rahul

3,914
1
14
25

1

`iasc`/`idesc` doesn't account for ties unfortunately and OP doesn't specifically preclude ties. Take for example third largest: `6~a (idesc a:4 2 5 1 6 6 8 8)[3-1]` , correct is `5~` – Daniel Krizian Nov 24 '18 at 09:27
Good catch. Using 'distinct' on list before iasc/idesc will fix it. – Rahul Nov 24 '18 at 17:27

score 1 · Answer 2 · edited Nov 29 '17 at 17:07

you can try using rank for this:

q)a:10?100
q)a
65 93 15 82 76 14 75 78 44 79
q)f:{x where y=rank x}
q)f[a;1]              / second smallest
,15
q)f[a;2]              / third smallest
,44

I don't think there is a reverse rank function in q so you can do this:

q)f2:{x where y=iasc idesc x}
q)f2[a;1]             / second biggest
,82
q)f2[a;2]             / third biggest
,79

http://code.kx.com/q/ref/sort/#rank

Choose second largest or smallest number in KDB

2 Answers2