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.