Re: query help

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>

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