gordon_t_wu_at_yahoo.com (Tao) wrote in
news:5db74e62.0407251625.65fbfcc5_at_posting.google.com:
> gordon_t_wu_at_yahoo.com (Tao) wrote in message
> news:<5db74e62.0407251027.76919b7b_at_posting.google.com>...
>> Mladen Gogala <gogala_at_sbcglobal.net> wrote in message
>> news:<pan.2004.07.24.02.10.00.257595_at_sbcglobal.net>...
>> > On Fri, 23 Jul 2004 18:41:47 -0700, Tao wrote:
>> >
>> > > Hi experts,
>> > >
>> > > I know its probably a simple query, but I've spent a long time on
>> > > it and could not figure it out.
>> > >
>> > >
>> > > I'm trying to select distinct fieldA in the table, such that
>> > > fieldB is smallest within each fieldA. Suppose the table has
>> > > following rows:
>> > >
>> > > P_Key fieldA fieldB
>> > > 1 1 5
>> > > 2 1 4
>> > > 3 2 4
>> > > 4 2 3
>> > >
>> > > The result of the query would look like:
>> > >
>> > > P_Key fieldA fieldB
>> > > 2 1 4
>> > > 4 2 3
>> > >
>> > > Appreciate your time
>> > >
>> > > FA
>> >
>> >
>> > select * from table
>> > where pk in
>> > (select pk from table where (fieldA,fieldB) in
>> > (select fieldA,min(fieldB) from table group by fieldA);
>> >
>> >
>> > It looks complicated, but it isn't. Here is the "proof of concept"
>> > query:
>> >
>> > $ sqlplus scott/tiger
>> >
>> > SQL*Plus: Release 10.1.0.2.0 - Production on Fri Jul 23 22:07:17
>> > 2004
>> >
>> > Copyright (c) 1982, 2004, Oracle. All rights reserved.
>> >
>> >
>> > Connected to:
>> > Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 -
>> > Production With the Partitioning, OLAP and Data Mining options
>> >
>> > SQL> select ename,deptno from emp
>> > 2 where (deptno,empno) in
>> > 3 (select deptno,min(empno) from emp group by deptno)
>> > 4 /
>> >
>> > ENAME DEPTNO
>> > ---------- ----------
>> > CLARK 10
>> > SMITH 20
>> > ALLEN 30
>> >
>> > SQL>
>>
>> Thank you Mladen, I think I understand this concept now. However,
>> what if (fiedA, min(fieldB)) is not unique and I do not want to
>> include the duplicates.
>> This is really part of much larger query, let me illustrate my point
>> using the following spool file:
>>
>>
>> SQL> create table my_table (pk number(2) primary key,
>> 2 fieldA number(2),
>> 3 fieldB number(4));
>>
>> Table created.
>>
>> SQL> insert into my_table (pk, fieldA, fieldB) values (1, 1, 1999);
>>
>> 1 row created.
>>
>> SQL> insert into my_table (pk, fieldA, fieldB) values (2, 1, 1999);
>>
>> 1 row created.
>>
>> SQL> commit;
>>
>> Commit complete.
>>
>> 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?
>
> One clarification, I know that a select count(distinct fieldA) would
> do. But I'm really not trying to perform a count here, I need to
> somehow have the subquery return me the rows with distinct fieldA
>
Not the way you are trying to solve this.
Try matching ROWIDs between the main query & the sub-query.
Received on Sun Jul 25 2004 - 19:43:07 CDT
