How do I get one record from a group for a maximum value?

C1(PK)  C2        C3                C4
=====================
1           psn-a    10/10/2005    Y
3           psn-b    09/09/2009    N
5           psn-a    10/10/2006    Y

>From the table above, I want to get columns C2, C3, and C4 for records

which have the same
value for C2 and C3 is the maximum. The results should be:

3 psn-b 09/09/2000 N
5 psn-a 10/10/2006 Y

I tried various group by but wasn't able to make it work (perhaps I'm confused).
In pseudo code this would be:

Get the columns C2, C3, C4 for a particular C2 where C3 has the maximum value.

Any suggestions (along with sample SQL are really welcome)

P.S.
This is a general query and not Oracle specific. However, I didn't find a better group to post this. Received on Wed Oct 25 2006 - 20:10:05 CDT