tricky SQL question!

[JZ]

Oracle 9iR2

I have a table:

SQLselect * 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!

[VC]

Re: tricky SQL question!

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" <ibm_97@yahoo.c om>
Newsgroups: comp.databases. oracle
Sent: Thursday, February 05, 2004 5:09 PM
Subject: tricky SQL question!

Oracle 9iR2
>
I have a table:
>
SQLselect * 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!