SQL Max Question

Question

So, I want to get the row with the most recent date max (asofdate), but MySQL is illiterate and seems to be unable to get it. This is my head, select * from Reports.InternalLoanExposureFlat, where asofdate = max (asofdate) seems to make sense, but the console does not seem to agree with me.

Thanks in advance.

Answer 1

If you do not want to risk returning multiple results, you should use this:

SELECT * FROM Reports.InternalLoanExposureFlat ORDER BY asofdate DESC LIMIT 1 

Answer 2

Try:

 SELECT * FROM Reports.InternalLoanExposureFlat WHERE asofdate = (SELECT MAX(asofdate) FROM Reports.InternalLoanExposureFlat) 

Answer 3

I agree with the console; -).

The max function returns the maximum of a group or full table.

Try:

 SELECT somecolumn, MAX(asofdate) FROM mytable GROUP BY somecolumn SELECT MAX(asofdate) FROM mytable