Sometimes, we want to fetch the row which has the max value for a column with SQL.
In this article, we’ll look at how to fetch the row which has the max value for a column with SQL.
How to fetch the row which has the max value for a column with SQL?
To fetch the row which has the max value for a column with SQL, we can do a self join.
For instance, we write
SELECT t1.*
FROM table t1
LEFT OUTER JOIN table t2
ON (t1.UserId = t2.UserId AND t1."Date" < t2."Date")
WHERE t2.UserId IS NULL;
to do a left join with to get the row with the max Date
column value with
LEFT OUTER JOIN table t2
ON (t1.UserId = t2.UserId AND t1."Date" < t2."Date")
Date
is in quotes because it’s a reserved word.
We use (t1.UserId = t2.UserId AND t1."Date" < t2."Date")
to match the values with the same UserId
but a greater Date
value.
Conclusion
To fetch the row which has the max value for a column with SQL, we can do a self join.