You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by Prasad Bhalerao <pr...@gmail.com> on 2018/07/26 15:18:17 UTC
SQL to limit number of records per agId
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
Re: SQL to limit number of records per agId
Posted by vkulichenko <va...@gmail.com>.
You can try using UNION for this:
(select * from cache1 where agid = 100 limit 2)
union
(select * from cache1 where agid = 101 limit 2)
https://gist.github.com/vkulichenko/8de603b28aa784ede84150614003e3a6
-Valid
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: SQL to limit number of records per agId
Posted by Prem Prakash Sharma <pr...@infoworks.io>.
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 <pr...@gmail.com>
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 <
> stephen.darlington@gridgain.com> 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 <pr...@gmail.com>
>> 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
>>
>>
>>
>>
Re: SQL to limit number of records per agId
Posted by Prasad Bhalerao <pr...@gmail.com>.
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 <
stephen.darlington@gridgain.com> 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 <pr...@gmail.com>
> 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
>
>
>
>
Re: SQL to limit number of records per agId
Posted by Stephen Darlington <st...@gridgain.com>.
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 <pr...@gmail.com> 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