You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by Courtney Robinson <co...@hypi.io> on 2020/07/04 18:17:29 UTC
SELECT values of each row within groups on a table with composite
primary key
I've posted this question on Stackoverflow here
https://stackoverflow.com/questions/62732258/select-values-of-each-row-within-groups-on-a-table-with-composite-primary-key
Copying for convenience:
I'm using Ignite 2.8.1 I have a table T1(a,b,c) with both a and b as
primary columns. I want to know the value of each b in each of the group.
Normally this would be fine since the primary key is functionally dependent
on the grouped column c in this case but Ignite's returning an error saying
b must be one of group by'a columns...which wouldn't be what I want, in
fact that'd be the same as not grouping.
Using the available SELECT <https://apacheignite-sql.readme.io/docs/select> -
can you suggest how to get Ignite to produce both a and b for each group,
or even just b. It happily produces a as if it is the only column in the
primary key.
[image: Screenshot 2020-07-04 at 19.16.53.png]
Any thoughts?
Regards,
Courtney Robinson
Founder and CEO, Hypi
Tel: ++44 208 123 2413 (GMT+0) <https://hypi.io>
<https://hypi.io>
https://hypi.io
Re: SELECT values of each row within groups on a table with composite
primary key
Posted by Courtney Robinson <co...@hypi.io>.
Thanks for replying.
The stackoverflow question was answered.
SELECT a, b, c, cntFROM T1 INNER JOIN (
> SELECT c, COUNT(c) as cnt
> FROM T1
> GROUP BY c) countsON counts.c = c
>
> The above produces the aggregate value as well as each row which
contributed to the aggregate.
On Mon, Jul 6, 2020 at 9:42 AM Stephen Darlington <
stephen.darlington@gridgain.com> wrote:
> I’m not sure I understand the question. Can you give an example of the
> source data and the results you’re expecting?
>
> On 4 Jul 2020, at 19:17, Courtney Robinson <co...@hypi.io>
> wrote:
>
> I've posted this question on Stackoverflow here
> https://stackoverflow.com/questions/62732258/select-values-of-each-row-within-groups-on-a-table-with-composite-primary-key
>
>
> Copying for convenience:
>
> I'm using Ignite 2.8.1 I have a table T1(a,b,c) with both a and b as
> primary columns. I want to know the value of each b in each of the group.
>
> Normally this would be fine since the primary key is functionally
> dependent on the grouped column c in this case but Ignite's returning an
> error saying b must be one of group by'a columns...which wouldn't be what
> I want, in fact that'd be the same as not grouping.
>
> Using the available SELECT
> <https://apacheignite-sql.readme.io/docs/select> - can you suggest how to
> get Ignite to produce both a and b for each group, or even just b. It
> happily produces a as if it is the only column in the primary key.
> <Screenshot 2020-07-04 at 19.16.53.png>
> Any thoughts?
>
> Regards,
> Courtney Robinson
> Founder and CEO, Hypi
> Tel: ++44 208 123 2413 (GMT+0) <https://hypi.io/>
>
> <https://hypi.io/>
> https://hypi.io
>
>
>
>
Re: SELECT values of each row within groups on a table with composite
primary key
Posted by Stephen Darlington <st...@gridgain.com>.
I’m not sure I understand the question. Can you give an example of the source data and the results you’re expecting?
> On 4 Jul 2020, at 19:17, Courtney Robinson <co...@hypi.io> wrote:
>
> I've posted this question on Stackoverflow here https://stackoverflow.com/questions/62732258/select-values-of-each-row-within-groups-on-a-table-with-composite-primary-key <https://stackoverflow.com/questions/62732258/select-values-of-each-row-within-groups-on-a-table-with-composite-primary-key>
>
> Copying for convenience:
>
> I'm using Ignite 2.8.1 I have a table T1(a,b,c) with both a and b as primary columns. I want to know the value of each b in each of the group.
>
> Normally this would be fine since the primary key is functionally dependent on the grouped column c in this case but Ignite's returning an error saying b must be one of group by'a columns...which wouldn't be what I want, in fact that'd be the same as not grouping.
>
> Using the available SELECT <https://apacheignite-sql.readme.io/docs/select> - can you suggest how to get Ignite to produce both a and b for each group, or even just b. It happily produces a as if it is the only column in the primary key.
>
> <Screenshot 2020-07-04 at 19.16.53.png>
> Any thoughts?
>
> Regards,
> Courtney Robinson
> Founder and CEO, Hypi
> Tel: ++44 208 123 2413 (GMT+0) <https://hypi.io/>
>
> <https://hypi.io/>https://hypi.io <https://hypi.io/>