SQL Max Question

Question

So I want to get the row with the most recent date, max(asofdate) but I am MySQL illiterate and can't seem to get it. It my head select * from Reports.InternalLoanExposureFlat where asofdate = max(asofdate) seems to make sense but the console seems to disagree with me.

Thanks in advance.

Answer 1

Try:

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

Answer 2

I agree with the console ;-).

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

Try :

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

SELECT MAX(asofdate) FROM mytable

Answer 3

If you don't want the risk of returning multiple results then you should use this:

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