Search code examples
sqlignite

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

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.

Solution

  • You might just want to precalculate counts for each group and then add this information for each a & b combination:

    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