Discussion on: #SQL30 Day 1: Wildfires

View post

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:

SELECT *
FROM day1.wildfire
WHERE (cause, acres) IN (
  SELECT cause, MAX(acres) AS acres
  FROM day1.wildfire
  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