SELECT values of each row within groups on a table with composite primary key

classic Classic list List threaded Threaded
3 messages Options
Courtney Robinson Courtney Robinson
Reply | Threaded
Open this post in threaded view
|

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

https://hypi.io
stephendarlington stephendarlington
Reply | Threaded
Open this post in threaded view
|

Re: SELECT values of each row within groups on a table with composite primary key

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 <[hidden email]> 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 - 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

https://hypi.io


Courtney Robinson Courtney Robinson
Reply | Threaded
Open this post in threaded view
|

Re: SELECT values of each row within groups on a table with composite primary key

Thanks for replying.
The stackoverflow question was answered.

SELECT a, b, c, cnt
FROM T1 
INNER JOIN (
  SELECT c, COUNT(c) as cnt 
  FROM T1 
  GROUP BY c
) counts
ON 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 <[hidden email]> 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 <[hidden email]> 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 - 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

https://hypi.io