SQL statement to get value from row containing max value

Submitted by Darren Oh on Tue, 04/05/2016 - 11:56

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:

SELECT d.* FROM field_data d
LEFT JOIN field_data dd ON d.revision_id = dd.revision_id AND d.delta < dd.delta
WHERE dd.revision_id IS NULL

This joins the table to itself and only returns records for which the joined table does not have a higher delta value. The effect is to return only the records with the highest delta values for their revision IDs. This query was successful.