Hi SQL Masters,

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 help!

select el2,min(el3||' '||el1) from test group by el2

where el2 is fieldA and el3 is fieldB and el1 is P_Key

I know there is a better way


"Tao" <gordon_t_wu (AT) yahoo (DOT) com> wrote

here is a better answer



1 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)
SQL> /

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



"Tao" <gordon_t_wu (AT) yahoo (DOT) com> wrote