SQL statement to get value from row containing max value
I was trying to get a list of highest numbered records from an SQL database table. My first attempt used a GROUP BY and an aggregate function:
SELECT * FROM field_data
GROUP BY revision_id
HAVING delta = MAX(delta)
This means to only return records which contain the highest delta values their revision IDs. I know SELECT statements are only supposed to have fields which appear in the GROUP BY clause, but I tried it just in case. The HAVING filter had no effect. My second attempt used a join: