# How to select Top-k records in each group?

**URL:** <https://forum.kx.com/t/how-to-select-top-k-records-in-each-group/8672>\
**Category:** Community Support\
**Tags:** kdb-and-q\
**Created:** [March 24, 2014, 2:52pm UTC](https://forum.kx.com/t/how-to-select-top-k-records-in-each-group/8672 "2014-03-24T14:52:00Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![afancy](https://avatars.discourse-cdn.com/v4/letter/a/f14d63/32.png) [@afancy](https://forum.kx.com/u/afancy)\
**Post date:** [March 24, 2014, 2:52pm UTC](https://forum.kx.com/t/how-to-select-top-k-records-in-each-group/8672/1 "2014-03-24T14:52:00Z")

</div>

Hi,&nbsp;

I have a table `data , and sorted by `Id1`value. Now I want to select Top-k records (e.g., k=2) in each group. How could I select? thanks!

q)data

id1 &nbsp; &nbsp;id2 &nbsp; &nbsp;value

* * *

1 &nbsp; &nbsp; &nbsp; 2 &nbsp; &nbsp; &nbsp; 0.5 &nbsp; &nbsp; &nbsp;&nbsp;

1 &nbsp; &nbsp; &nbsp; 3 &nbsp; &nbsp; &nbsp; 0.6 &nbsp; &nbsp; &nbsp;&nbsp;

1 &nbsp; &nbsp; &nbsp; 4 &nbsp; &nbsp; &nbsp; 0.1 &nbsp; &nbsp; &nbsp;&nbsp;

2 &nbsp; &nbsp; &nbsp; 1 &nbsp; &nbsp; &nbsp; 0.3 &nbsp; &nbsp; &nbsp;&nbsp;

2 &nbsp; &nbsp; &nbsp; 3 &nbsp; &nbsp; &nbsp; 0.32 &nbsp; &nbsp; &nbsp;

2 &nbsp; &nbsp; &nbsp; 5 &nbsp; &nbsp; &nbsp; 0.42 &nbsp; &nbsp; &nbsp;

q)`id1`value&nbsp;xdesc data

id1 &nbsp; &nbsp;id2 &nbsp; &nbsp; value

* * *

2 &nbsp; &nbsp; &nbsp; 5 &nbsp; &nbsp; &nbsp; 0.42 &nbsp; &nbsp; &nbsp;

2 &nbsp; &nbsp; &nbsp; 3 &nbsp; &nbsp; &nbsp; 0.32 &nbsp; &nbsp; &nbsp;

2 &nbsp; &nbsp; &nbsp; 1 &nbsp; &nbsp; &nbsp; 0.3 &nbsp; &nbsp; &nbsp;&nbsp;

1 &nbsp; &nbsp; &nbsp; 3 &nbsp; &nbsp; &nbsp; 0.6 &nbsp; &nbsp; &nbsp;&nbsp;

1 &nbsp; &nbsp; &nbsp; 2 &nbsp; &nbsp; &nbsp; 0.5 &nbsp; &nbsp; &nbsp;&nbsp;

1 &nbsp; &nbsp; &nbsp; 4 &nbsp; &nbsp; &nbsp; 0.1 &nbsp; &nbsp; &nbsp;&nbsp;

---

<div class="post-metadata">

**Author:** ![manni\_patel](https://avatars.discourse-cdn.com/v4/letter/m/958977/32.png) [@manni\_patel](https://forum.kx.com/u/manni_patel)\
**Post date:** [March 24, 2014, 3:01pm UTC](https://forum.kx.com/t/how-to-select-top-k-records-in-each-group/8672/2 "2014-03-24T15:01:00Z")

</div>

select [k] from …

---

<div class="post-metadata">

**Author:** ![manni\_patel](https://avatars.discourse-cdn.com/v4/letter/m/958977/32.png) [@manni\_patel](https://forum.kx.com/u/manni_patel)\
**Post date:** [March 24, 2014, 3:02pm UTC](https://forum.kx.com/t/how-to-select-top-k-records-in-each-group/8672/3 "2014-03-24T15:02:00Z")

</div>

…by id1, …

:) I didn’t read the question properly the first time

---

<div class="post-metadata">

**Author:** ![sohagan8571](https://avatars.discourse-cdn.com/v4/letter/s/ba9def/32.png) [@sohagan8571](https://forum.kx.com/u/sohagan8571)\
**Post date:** [March 24, 2014, 3:03pm UTC](https://forum.kx.com/t/how-to-select-top-k-records-in-each-group/8672/4 "2014-03-24T15:03:00Z")

</div>

Group of what?

---

<div class="post-metadata">

**Author:** ![afancy](https://avatars.discourse-cdn.com/v4/letter/a/f14d63/32.png) [@afancy](https://forum.kx.com/u/afancy)\
**Post date:** [March 24, 2014, 4:09pm UTC](https://forum.kx.com/t/how-to-select-top-k-records-in-each-group/8672/5 "2014-03-24T16:09:00Z")

</div>

On Monday, March 24, 2014 10:52:20 AM UTC-4, afancy wrote:

> Hi,&nbsp;
> 
> I have a table `data , and sorted by `Id1`value. Now I want to select Top-k records (e.g., k=2) in each group. How could I select? thanks!
> 
> q)data
> 
> id1 &nbsp; &nbsp;id2 &nbsp; &nbsp;value
> 
> * * *
> 
> 1 &nbsp; &nbsp; &nbsp; 2 &nbsp; &nbsp; &nbsp; 0.5 &nbsp; &nbsp; &nbsp;&nbsp;
> 
> 1 &nbsp; &nbsp; &nbsp; 3 &nbsp; &nbsp; &nbsp; 0.6 &nbsp; &nbsp; &nbsp;&nbsp;
> 
> 1 &nbsp; &nbsp; &nbsp; 4 &nbsp; &nbsp; &nbsp; 0.1 &nbsp; &nbsp; &nbsp;&nbsp;
> 
> 2 &nbsp; &nbsp; &nbsp; 1 &nbsp; &nbsp; &nbsp; 0.3 &nbsp; &nbsp; &nbsp;&nbsp;
> 
> 2 &nbsp; &nbsp; &nbsp; 3 &nbsp; &nbsp; &nbsp; 0.32 &nbsp; &nbsp; &nbsp;
> 
> 2 &nbsp; &nbsp; &nbsp; 5 &nbsp; &nbsp; &nbsp; 0.42 &nbsp; &nbsp; &nbsp;
> 
> q)`id1`value&nbsp;xdesc data
> 
> id1 &nbsp; &nbsp;id2 &nbsp; &nbsp; value
> 
> * * *
> 
> 2 &nbsp; &nbsp; &nbsp; 5 &nbsp; &nbsp; &nbsp; 0.42 &nbsp; &nbsp; &nbsp;
> 
> 2 &nbsp; &nbsp; &nbsp; 3 &nbsp; &nbsp; &nbsp; 0.32 &nbsp; &nbsp; &nbsp;
> 
> 2 &nbsp; &nbsp; &nbsp; 1 &nbsp; &nbsp; &nbsp; 0.3 &nbsp; &nbsp; &nbsp;&nbsp;
> 
> 1 &nbsp; &nbsp; &nbsp; 3 &nbsp; &nbsp; &nbsp; 0.6 &nbsp; &nbsp; &nbsp;&nbsp;
> 
> 1 &nbsp; &nbsp; &nbsp; 2 &nbsp; &nbsp; &nbsp; 0.5 &nbsp; &nbsp; &nbsp;&nbsp;
> 
> 1 &nbsp; &nbsp; &nbsp; 4 &nbsp; &nbsp; &nbsp; 0.1 &nbsp; &nbsp; &nbsp;&nbsp;

The group is based on the values of id1. In this case, there are two groups, group one with id1=1, and group two with id1=2. I want to return top-k records in each group. In this example, if k=2, the result that I want is:

id1 &nbsp; &nbsp;id2 &nbsp; &nbsp; value

* * *

2 &nbsp; &nbsp; &nbsp; 5 &nbsp; &nbsp; &nbsp; 0.42 &nbsp; &nbsp; &nbsp;

2 &nbsp; &nbsp; &nbsp; 3 &nbsp; &nbsp; &nbsp; 0.32 &nbsp; &nbsp; &nbsp;

1 &nbsp; &nbsp; &nbsp; 3 &nbsp; &nbsp; &nbsp; 0.6 &nbsp; &nbsp; &nbsp;&nbsp;

1 &nbsp; &nbsp; &nbsp; 2 &nbsp; &nbsp; &nbsp; 0.5 &nbsp; &nbsp; &nbsp;&nbsp;

---

<div class="post-metadata">

**Author:** ![tianye75](https://avatars.discourse-cdn.com/v4/letter/t/7ab992/32.png) [@tianye75](https://forum.kx.com/u/tianye75)\
**Post date:** [March 24, 2014, 4:21pm UTC](https://forum.kx.com/t/how-to-select-top-k-records-in-each-group/8672/6 "2014-03-24T16:21:00Z")

</div>

data raze 0 1+/:where differ data`id1 ??

Ye

---

<div class="post-metadata">

**Author:** ![sohagan8571](https://avatars.discourse-cdn.com/v4/letter/s/ba9def/32.png) [@sohagan8571](https://forum.kx.com/u/sohagan8571)\
**Post date:** [March 24, 2014, 4:32pm UTC](https://forum.kx.com/t/how-to-select-top-k-records-in-each-group/8672/7 "2014-03-24T16:32:00Z")

</div>

won’t be as fast as Ye’s but simply set yourself up with functional select:

f:{?[z;();(x,())!x,();(y,())!{(#;2;x)}each y]}

ungroup f[`id1;`id2`value;`id1`value xdesc data]

HTH, Sean.

---

<div class="post-metadata">

**Author:** ![kuentang](https://avatars.discourse-cdn.com/v4/letter/k/bc8723/32.png) [@kuentang](https://forum.kx.com/u/kuentang)\
**Post date:** [March 24, 2014, 4:37pm UTC](https://forum.kx.com/t/how-to-select-top-k-records-in-each-group/8672/8 "2014-03-24T16:37:00Z")

</div>

data:flip `id1`id2`value!(1 1 1 2 2 2;2 3 4 1 3 5;0.5 0.6 0.1 0.3 0.32 0.42)\<o:p\>\</o:p\>

ungroup `id1 xdesc ?[`id1`value xdesc data;();b!b:enlist `id1;] a!(2#),/:&nbsp; a: cols[data] except `id1\<o:p\>\</o:p\>

\<o:p\> \</o:p\>

You should avoid using value as a column name.\<o:p\>\</o:p\>

\<o:p\> \</o:p\>

HTH,\<o:p\>\</o:p\>

\<o:p\> \</o:p\>

Kim\<o:p\>\</o:p\>

\<o:p\> \</o:p\>

**Von:** [personal-kdbplus@googlegroups.com](mailto:personal-kdbplus@googlegroups.com) [[mailto:personal-kdbplus@googlegroups.com](mailto:personal-kdbplus@googlegroups.com)] **Im Auftrag von** afancy  
**Gesendet:** Montag, 24. März 2014 17:09  
**An:** [personal-kdbplus@googlegroups.com](mailto:personal-kdbplus@googlegroups.com)  
**Betreff:** [personal kdb+] Re: How to select Top-k records in each group?\<o:p\>\</o:p\>

\<o:p\> \</o:p\>

On Monday, March 24, 2014 10:52:20 AM UTC-4, afancy wrote:\<o:p\>\</o:p\>

Hi,&nbsp;\<o:p\>\</o:p\>

\<o:p\> \</o:p\>

I have a table `data , and sorted by `Id1`value. Now I want to select Top-k records (e.g., k=2) in each group. How could I select? thanks!\<o:p\>\</o:p\>

\<o:p\> \</o:p\>

q)data\<o:p\>\</o:p\>

id1 &nbsp; &nbsp;id2 &nbsp; &nbsp;value\<o:p\>\</o:p\>

--------------------------\<o:p\>\</o:p\>

1 &nbsp; &nbsp; &nbsp; 2 &nbsp; &nbsp; &nbsp; 0.5 &nbsp; &nbsp; &nbsp;&nbsp;\<o:p\>\</o:p\>

1 &nbsp; &nbsp; &nbsp; 3 &nbsp; &nbsp; &nbsp; 0.6 &nbsp; &nbsp; &nbsp;&nbsp;\<o:p\>\</o:p\>

1 &nbsp; &nbsp; &nbsp; 4 &nbsp; &nbsp; &nbsp; 0.1 &nbsp; &nbsp; &nbsp;&nbsp;\<o:p\>\</o:p\>

2 &nbsp; &nbsp; &nbsp; 1 &nbsp; &nbsp; &nbsp; 0.3 &nbsp; &nbsp; &nbsp;&nbsp;\<o:p\>\</o:p\>

2 &nbsp; &nbsp; &nbsp; 3 &nbsp; &nbsp; &nbsp; 0.32 &nbsp; &nbsp; &nbsp;\<o:p\>\</o:p\>

2 &nbsp; &nbsp; &nbsp; 5 &nbsp; &nbsp; &nbsp; 0.42 &nbsp; &nbsp; &nbsp;\<o:p\>\</o:p\>

\<o:p\> \</o:p\>

q)`id1`value&nbsp;xdesc data\<o:p\>\</o:p\>

id1 &nbsp; &nbsp;id2 &nbsp; &nbsp; value\<o:p\>\</o:p\>

--------------------------\<o:p\>\</o:p\>

2 &nbsp; &nbsp; &nbsp; 5 &nbsp; &nbsp; &nbsp; 0.42 &nbsp; &nbsp; &nbsp;\<o:p\>\</o:p\>

2 &nbsp; &nbsp; &nbsp; 3 &nbsp; &nbsp; &nbsp; 0.32 &nbsp; &nbsp; &nbsp;\<o:p\>\</o:p\>

2 &nbsp; &nbsp; &nbsp; 1 &nbsp; &nbsp; &nbsp; 0.3 &nbsp; &nbsp; &nbsp;&nbsp;\<o:p\>\</o:p\>

1 &nbsp; &nbsp; &nbsp; 3 &nbsp; &nbsp; &nbsp; 0.6 &nbsp; &nbsp; &nbsp;&nbsp;\<o:p\>\</o:p\>

1 &nbsp; &nbsp; &nbsp; 2 &nbsp; &nbsp; &nbsp; 0.5 &nbsp; &nbsp; &nbsp;&nbsp;\<o:p\>\</o:p\>

1 &nbsp; &nbsp; &nbsp; 4 &nbsp; &nbsp; &nbsp; 0.1 &nbsp; &nbsp; &nbsp;&nbsp;\<o:p\>\</o:p\>

\<o:p\> \</o:p\>

\<o:p\> \</o:p\>

The group is based on the values of id1. In this case, there are two groups, group one with id1=1, and group two with id1=2. I want to return top-k records in each group. In this example, if k=2, the result that I want is:\<o:p\>\</o:p\>

id1 &nbsp; &nbsp;id2 &nbsp; &nbsp; value\<o:p\>\</o:p\>

--------------------------\<o:p\>\</o:p\>

2 &nbsp; &nbsp; &nbsp; 5 &nbsp; &nbsp; &nbsp; 0.42 &nbsp; &nbsp; &nbsp;\<o:p\>\</o:p\>

2 &nbsp; &nbsp; &nbsp; 3 &nbsp; &nbsp; &nbsp; 0.32 &nbsp; &nbsp; &nbsp;\<o:p\>\</o:p\>

1 &nbsp; &nbsp; &nbsp; 3 &nbsp; &nbsp; &nbsp; 0.6 &nbsp; &nbsp; &nbsp;&nbsp;\<o:p\>\</o:p\>

1 &nbsp; &nbsp; &nbsp; 2 &nbsp; &nbsp; &nbsp; 0.5 &nbsp; &nbsp; &nbsp;&nbsp;\<o:p\>\</o:p\>

–  
Submitted via Google Groups

---

<div class="post-metadata">

**Author:** ![WooiKent\_Lee](https://avatars.discourse-cdn.com/v4/letter/w/5f8ce5/32.png) [@WooiKent\_Lee](https://forum.kx.com/u/WooiKent_Lee)\
**Post date:** [March 24, 2014, 5:11pm UTC](https://forum.kx.com/t/how-to-select-top-k-records-in-each-group/8672/9 "2014-03-24T17:11:00Z")

</div>

I’m using Kim’s example:

q)f:{select from data where fby[({iasc idesc x};val);id1] in til x}

q)f 2

id1 id2 val

* * *

1 &nbsp; 2 &nbsp; 0.5

1 &nbsp; 3 &nbsp; 0.6

2 &nbsp; 3 &nbsp; 0.32

2 &nbsp; 5 &nbsp; 0.42

q)f 1

id1 id2 val

* * *

1 &nbsp; 3 &nbsp; 0.6

2 &nbsp; 5 &nbsp; 0.42