Can you add a temporary rank column add rank for columns for every ageid starting from 1 till number of rows with that ageid then select where rank < configurable_num
Regards, Prem On Thu, Jul 26, 2018, 11:05 PM Prasad Bhalerao <[email protected]> wrote: > Hi Stephen > Thank you for the solution. > But it is limiting the no. Of record per agid to 1. > > I want to select 2 to 3 ( or some configurable no. Of records) record per > agid. > > Any idea how it can be done? > > > On Thu, Jul 26, 2018, 9:52 PM Stephen Darlington < > [email protected]> wrote: > >> How about: >> >> 0: jdbc:ignite:thin://127.0.0.1/> select * from cache1; >> 'ID','AGID','VAL' >> '1','100','10-15' >> '2','100','17-20' >> '3','100','30-50' >> '4','101','10-15' >> '5','101','17-20' >> 5 rows selected (0.003 seconds) >> 0: jdbc:ignite:thin://127.0.0.1/> select * from cache1 where id in >> (select min(id) from cache1 group by agid); >> 'ID','AGID','VAL' >> '1','100','10-15' >> '4','101','10-15' >> 2 rows selected (0.004 seconds) >> 0: jdbc:ignite:thin://127.0.0.1/> >> >> Regards, >> Stephen >> >> On 26 Jul 2018, at 16:18, Prasad Bhalerao <[email protected]> >> wrote: >> >> Hi, >> I have cache CAHE_1 and it has entries as follows. ID is the cache key >> and agID and Val are part the values. >> >> Now I want to query this cache to get the output as shown below(Sample >> output). >> In short I want to limit the number of rows per agId. I just want to >> fetch 1 to 3 rows per agId. >> >> Filter for this sql will be "agid in (100,101)". >> >> I understand that this is H2 Db related question but I did not find any >> working solution for this. >> Can some one please advise? >> >> >> Cache Entries >> ID | agId | Val >> --------------- >> 1 | 100 |10-15 >> 2 | 100 |17-20 >> 3 | 100 |30-50 >> 4 | 101 |10-15 >> 5 | 101 |17-20 >> >> Sample output: >> ID | Val | Val >> ---------------------- >> 1 | 100 | 10-15 >> 4 | 101 | 10-15 >> >> Thanks, >> Prasad >> >> >> >>
