470,874 Members | 1,509 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,874 developers. It's quick & easy.

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!
Jul 19 '05 #1
2 2109
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" <go*********@yahoo.com> wrote in message
news:5d*************************@posting.google.co m...
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!

Jul 19 '05 #2
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" <go*********@yahoo.com> wrote in message
news:5d*************************@posting.google.co m...
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!

Jul 19 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

9 posts views Thread by netpurpose | last post: by
7 posts views Thread by Simon Bailey | last post: by
36 posts views Thread by Liam.M | last post: by
4 posts views Thread by Doris | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.