[sergeykish]

> The following table lists aggregate functions:

> count() Number of entries in column

> max() Maximum value in column

> The by clause causes the function to return a set of results, as opposed to a single result. One result is returned for each grouping specified by the by clause. Think of by as meaning "for each."

I assume it evaluates like retrieving set and scalar.

      a   |   b
    --------------
    set 1 | scalar
    set 2 | scalar

[1] http://docs.huihoo.com/ingres/9.3/QUELRef.pdf

[alquemist]

Assuming we key a and b by y.d and generalizing b to sets, we are reaching the limits of the relational model: there is no good way to represent multiple sets associated with a given key, we need independent tables to do so. Neither cross product nor null padding is a good way to represent the schema {y, a[], b[]}.

      y.d |  a   |  b
    -------------------
      y0  |  a0  |  b0
      y0  |  a0  |  b1
      y0  |  a1  |  b0
      y0  |  a1  |  b1
      y0  |  a2  |  b0
      y0  |  a2  |  b1

    
      y.d |  a   |  b
    -------------------
      y0  |  a0  |  b0
      y0  |  a1  |  b1
      y0  |  a2  |  null

[sergeykish]

    # create table y (i int, d int);
    # insert into y values (1, 1), (1, 2), (2, 1);
    # select *, (select count(*) from (select count(y.i), y.d from y group by y.d) _) as foo
      from (select count(y.i), y.d from y group by y.d) _ ;
    
     count | d | foo 
    -------+---+-----
         1 | 2 |   2
         2 | 1 |   2
    (2 rows)
    

By the way great example how unwieldy SQL is. A bit better with CTE:

    # with bar as (select count(y.i), y.d from y group by y.d)
      select *, (select count(*) from bar) as bar from foo;

[alquemist]

Nice! Should be easy to do max instead of count. Then top instead of max. Then top3 breaks the model.

[sergeykish]

Yes, should be max(count). I've lost you with top3.

    # select * from
      (select count from (select count(y.i), y.d from y group by y.d) _ limit 2) b,
      (select count(y.i), y.d from y group by y.d) a;
     count | count | d 
    -------+-------+---
         1 |     1 | 2
         2 |     1 | 2
         1 |     2 | 1
         2 |     2 | 1
    (4 rows)

[alquemist]

top - max value in the group

top3 - max 3 values in the group