does this have to be in SQL, personally I'd do this in the front end, but that may indicate my lack of knowledge of SQL

I'm pretty certain you can achieve this using a sub select

Not certain but DATE may be a reserved word in MySQL