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_at_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!
Received on Thu Feb 05 2004 - 20:05:28 CST