Fetch the row which has the Max value for a column

Question

Table:

UserId, Value, Date.

I want to get the UserId, Value for the max(Date) for each UserId. That is, the Value for each UserId that has the latest date. Is there a way to do this simply in SQL? (Preferably Oracle)

Update: Apologies for any ambiguity: I need to get ALL the UserIds. But for each UserId, only that row where that user has the latest date.

Answer 1

Use the following code:

select user_id,

       My_Date,

       ...

from

(

select user_id,

       My_Date,

       ...

       max(My_Date) over (partition by user_id) max_My_Date

from   users

)

where My_Date = max_My_Date

The above code will retrieve all rows for which the My_Date column value is equal to the maximum value of My_Date for that user_id.

If the maximum date is on multiple rows then, it’ll retrieve multiple rows for the user_id.