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.