Re: query help

On Sun, 25 Jul 2004 11:27:49 -0700, Tao wrote:

> SQL> select count(*)
> 2 from my_table
> 3 where pk in (select pk
> 4 from my_table
> 5 where (fieldA, fieldB) in (select fieldA,
> min(fieldB)
> 6 from my_table 7
> group by fieldA));
>
> COUNT(*)
> ----------
> 2
>
> SQL> spool off;
>
> As you can see, the count is 2 since fieldB are 1999 in both rows. Is
> there a way for me to get count to equal 1?

Off hand, I would try something like this: select * from table  where pk in
 (select pkey from

    (select min(pk) pkey,fieldA,min(fieldB) from table group by fieldA);

The added min(pk) will return only the minimum primary key, which is unique. You should, however, be aware that, by doing this, you are losing all the information from those other rows that get eliminated.

-- 
A city is a large community where people are lonesome together.