Re: query help

From: Michael J. Moore <DoNThicamelSPAM_at_comcast.net>
Date: Sat, 24 Jul 2004 16:37:56 GMT
Message-ID: <EfwMc.154343$%_6.8365@attbi_s01>

"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>
>

As mentioned on the cross post, here is another solution.

given this table ...
SQL> select * from test;

EL1                         EL2        EL3
-------------------- ---------- ----------
2                             1          4
3                             2          4
4                             2          3
5                             2          3
1                             1          5

SQL> select el1,el2,el3 from test a
  2 where
  3 not exists (select * from test b

  4              where b.el2 = a.el2
  5              and   b.el3 < a.el3);

EL1                         EL2        EL3
-------------------- ---------- ----------
2                             1          4
4                             2          3
5                             2          3

Also, as shown before by Mladen....

SQL> select * from test
  2 where el1 in
  3 (select el1 from test where(el2,el3) in   4 (select distinct el2,min(el3) from test group by el2))   5 /

EL1                         EL2        EL3
-------------------- ---------- ----------
2                             1          4
4                             2          3
5                             2          3

Note that both of these solutions will reproduce duplicate min values.
4                             2          3
5                             2          3

Here is one that only takes the lowest of EL1 when EL2 and EL3 have duplicates.
It is not very pretty and you have to know that EL3 will be a number < 100000000.

  1 SELECT mod(e3ANDe1,10000000) el1, el2, trunc(e3ANDe1/100000000) el3 FROM
  2* (SELECT el2, min(el3 * 100000000 + el1) e3ANDe1 FROM TEST group by el2)
SQL> /        EL1 EL2 EL3
---------- ---------- ----------

         2          1          4
         4          2          3

If el2 and el3 where character you could use CONCAT in the inner SELECT and SUBSTR to pull them apart again in the outer SELECT. Mike Received on Sat Jul 24 2004 - 11:37:56 CDT