Re: query help

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? Received on Sun Jul 25 2004 - 13:27:49 CDT