query help

[Tao]

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!

[Michael J. Moore]

Re: query 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@ya hoo.comwrote in message
news:5db74e62.0 407231737.36c70 03@posting.goog le.com...

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!

[Michael J. Moore]

Re: query help

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@ya hoo.comwrote in message
news:5db74e62.0 407231737.36c70 03@posting.goog le.com...

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!