4 ms·
# 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 b
by sergeykish 6y ago
# 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 6y agoNice! Should be easy to do max instead of count. Then top instead of max. Then top3 breaks the model.
- sergeykish 6y agoYes, 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 6y agotop - max value in the group top3 - max 3 values in the group