ProAnswers.org

Fetching the row which has Max value for a column?

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)

I see many people use subqueries or else vendor-specific features 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.

			SELECT t1.* 
			FROM mytable AS t1 
			  LEFT OUTER JOIN mytable AS t2 
			    ON (t1.UserId = t2.UserId AND t1."Date" < t2."Date") 
			WHERE t2.UserId IS NULL; 
			 
	



In other words: fetch the row from t1 where no other row exists with the same UserId and a greater Date.