SQL grouping and then un-grouping in one query

Hey I am trying to do a sub query that allows me to get a count of something by grouping, then basically un-group and select a certain amount of rows based on the count I just got. This query is basically what I want if SQL would allow something like a contains() method or something.

select cat, mouse
from pets
where cat = (select cat
             from开发者_StackOverflow社区 pets
             group by cat
             having count(mouse) > 3);

Any thoughts? Using oracle10g.

select cat, mouse
from pets
where cat in (select cat
         from pet
         group by cat
         having count(mouse) > 3);

Change '= 'to 'in' The in keyword is what this is made for.

Not that it's horribly wrong to use an in subquery, but an approach using analytic functions usually performs a lot better for this sort of self-joining query.

select cat, mouse from
(
    select cat, mouse, count(mouse) over(partition by cat) as ct
) 
where ct = 3