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 > > > >
