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
Received on Sun Jul 25 2004 - 19:25:41 CDT
