Not being at work, I don't have Oracle to hand, but I seem to recall that Oracle allows multiple columns to be matched in an IN clause, which should at least avoid the options that use a correlated subquery, which is seldom a good idea.

Something like this, perhaps (can't remember if the column list should be parenthesised or not):

SELECT * FROM MyTable WHERE (User, Date) IN ( SELECT User, MAX(Date) FROM MyTable GROUP BY User) 

Not being at work, I don't have Oracle to hand, but I seem to recall that Oracle allows multiple columns to be matched in an IN clause, which should at least avoid the options that use a correlated subquery, which is seldom a good idea.

Something like this, perhaps (can't remember if the column list should be parenthesised or not):

SELECT * FROM MyTable WHERE (User, Date) IN ( SELECT User, MAX(Date) FROM MyTable GROUP BY User) 

EDIT: Just tried it for real:

SQL> create table MyTable (usr char(1), dt date); SQL> insert into mytable values ('A','01-JAN-2009'); SQL> insert into mytable values ('B','01-JAN-2009'); SQL> insert into mytable values ('A', '31-DEC-2008'); SQL> insert into mytable values ('B', '31-DEC-2008'); SQL> select usr, dt from mytable 2 where (usr, dt) in 3 ( select usr, max(dt) from mytable group by usr) 4 / U DT - --------- A 01-JAN-09 B 01-JAN-09 

So it works, although some of the new-fangly stuff mentioned elsewhere may be more performant.