469,600 Members | 2,255 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

tricky SQL question!

JZ
Oracle 9iR2

I have a table:

SQL> select * from test;

A B C
------------------- ---------- ----------
01/01/2004 10:00:00 1 1
01/01/2004 11:00:00 1 2
01/01/2004 11:00:00 2 3
01/01/2004 13:00:00 2 4
01/01/2004 12:00:00 2 5

For every different B value, I want to see the max A value and the
corresponding C value. In the above example, the result shoud be:

A B C
---------------------- ---- ---
01/01/2004 11:00:00 1 2
01/01/2004 13:00:00 2 4
How can I do this in SQL (no PL/SQL or SQL block)?

Thanks a lot!
Jul 19 '05 #1
1 13569
VC
Well, it's rather trivial:

select * from t1 x
where a=(select max(a) from t1 where b=x.b)
VC

----- Original Message -----
From: "JZ" <ib****@yahoo.com>
Newsgroups: comp.databases.oracle
Sent: Thursday, February 05, 2004 5:09 PM
Subject: tricky SQL question!

Oracle 9iR2

I have a table:

SQL> select * from test;

A B C
------------------- ---------- ----------
01/01/2004 10:00:00 1 1
01/01/2004 11:00:00 1 2
01/01/2004 11:00:00 2 3
01/01/2004 13:00:00 2 4
01/01/2004 12:00:00 2 5

For every different B value, I want to see the max A value and the
corresponding C value. In the above example, the result shoud be:

A B C
---------------------- ---- ---
01/01/2004 11:00:00 1 2
01/01/2004 13:00:00 2 4
How can I do this in SQL (no PL/SQL or SQL block)?

Thanks a lot!

Jul 19 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Martin | last post: by
2 posts views Thread by Kennedy_f | last post: by
reply views Thread by Piotr Szukalski | last post: by
25 posts views Thread by PyPK | last post: by
8 posts views Thread by pras.vaidya | last post: by
14 posts views Thread by felixnielsen | last post: by
9 posts views Thread by howachen | last post: by
1 post views Thread by JZ | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.