bit late to the party! google pointed me to here and it seems interesting, hopefully I can get to the end...
given this is Postgres I'd use the DISTINCT ON operator, as it seems perfectly suited to the task:
SELECT DISTINCT ON (cause) * FROM wildfire ORDER BY cause, acres DESC;
in this case you can also use an IN query:
WHERE (cause, acres) IN (
SELECT cause, MAX(acres) AS acres
GROUP BY cause
ORDER BY acres DESC;
which gives me the same thing. but has different semantics so does different things in the presence of non-unique values. for some reason I thought it could do unexpected things when NULL values were involved, but a quick read suggests that's just NOT IN
We're a place where coders share, stay up-to-date and grow their careers.
We strive for transparency and don't collect excess data.