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.
Best Answer
I see many people use subqueries or else window functions to do this, but I often do this kind of query without subqueries in the following way. It uses plain, standard SQL so it should work in any brand of RDBMS.
In other words: fetch the row from
t1
where no other row exists with the sameUserId
and a greater Date.(I put the identifier "Date" in delimiters because it's an SQL reserved word.)
In case if
t1."Date" = t2."Date"
, doubling appears. Usually tables hasauto_inc(seq)
key, e.g.id
. To avoid doubling can be used follows:Re comment from @Farhan:
Here's a more detailed explanation:
An outer join attempts to join
t1
witht2
. By default, all results oft1
are returned, and if there is a match int2
, it is also returned. If there is no match int2
for a given row oft1
, then the query still returns the row oft1
, and usesNULL
as a placeholder for all oft2
's columns. That's just how outer joins work in general.The trick in this query is to design the join's matching condition such that
t2
must match the sameuserid
, and a greaterdate
. The idea being if a row exists int2
that has a greaterdate
, then the row int1
it's compared against can't be the greatestdate
for thatuserid
. But if there is no match -- i.e. if no row exists int2
with a greaterdate
than the row int1
-- we know that the row int1
was the row with the greatestdate
for the givenuserid
.In those cases (when there's no match), the columns of
t2
will beNULL
-- even the columns specified in the join condition. So that's why we useWHERE t2.UserId IS NULL
, because we're searching for the cases where no row was found with a greaterdate
for the givenuserid
.