What a great idea, thanks for setting this up :D
This may be a little convoluted but ....
I would check out how many causes there were first:
select distinct cause
order by cause
-- 13 rows
Then use a Window Function to order the fires by their cause and assign a rank in descending order.
Then use a CTE to isolate the Window Function results and return all rows where the rank = 1.
with rankings as
dense_rank() over(partition by cause order by acres desc) as fire_rank
order by cause, fire_rank
where rankings.fire_rank = 1
-- 20 seconds to execute
Then do some checks to make sure I've got the right results
select fire_name, max(acres) as acresmax
where cause = 'Campfire'
group by fire_name
order by acresmax desc
That's an awesome solution, thanks for writing that. I don't think it's convoluted at all.
In my solution I went without window functions and did a join to a subquery. The interesting thing is that I could potentially join to the wrong row if there were more than 1 fire with the same cause and max acres burned, whereas here I think any ties would end up with the same rank, but it's guaranteed that there's no mixing and matching of data.
I hope you like the rest of the series! I started it because I was one of those developers that thought they knew SQL, when really I knew SQL-92, and modern SQL has so much more to offer.
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.