Boneist's Oracle Blog

I tried to help someone out today on a Bulletin Board System that I’ve been on for years.

Their question was:

Here is my dataset:

select distinct id from table where id not in
  (select id from table where flag=0)
and  flag=1;

Seems overkill. Is there a better way?

I proposed:

select id
from   (select id, count(id) flag_count, sum(decode(flag, 1, 1, 0)) flag1_count
        from   my_tab
        group by id)
where  flag_count = flag1_count;

Which as any fule knows is exactly what the HAVING clause was meant to do:

select id
from   my_tab
group by id
having count(id) = sum(decode(flag, 1, 1, 0));

I seem to have a blind spot for this when it comes time to generating the SQL – sure, I know about the HAVING clause, but remembering it when I need it is an entirely different matter!

Sheesh!