Search code examples
kdb

KDB top rows for each group

I want to query top 5 salary earners form dept table SQL Like

    SELECT * FROM (
                    SELECT empno, salary, RANK () OVER (PARTITION BY deptno ORDER BY salary DESC) emprank FROM emp ) 
    WHERE emprank <= 3;

I was trying to do somthing like..

select from emp where ({x in 3#x};i) fby deptno

but not able to produce expected result. Please can you give me a clue.

Solution

  • Another option would be:

    select 3 sublist salary idesc[salary] by deptno from emp

    sublist was used rather than # to handle the case were a department has less than 3 salaries.