limiting a group by to the first 10 groups

[bberg@rtnyc.com]

if I do,
select colA, sum(colB)
from tableA
group by colA
order by sum(colB) desc

how do I get ONLY the first 10 groups (those with the largest sums)...

and DON'T tell me to do this:

select colA, sum(colB)
from tableA
where rownum < 11
group by colA
order by sum(colB) desc

because this will reduce the rows used in the grouping, not the groups itself.

[marist89]

Try this:
select colA, sum(colB)
from tableA
where rownum < 11
group by colA
order by sum(colB) desc

;)

No, really, try this:
select rownum, cola, scb
from (select colA, sum(colb) scb from table a group by cola order by scb desc)
where rownum <= 10

[bberg@rtnyc.com]

Uh, doesn't the inner select statement still bring back all of the groups...

ok, maybe this will work, but as for efficiency, it may not be all that great... perhaps if I use a FIRST_ROWS hint... maybe then...

i'll try it.