Welcome to the SQL showdown series!
What is this and how does it work?
I'm committing to publishing a SQL challenge every day for 30 ...
For further actions, you may consider blocking this person and/or reporting abuse
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:
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.
Then do some checks to make sure I've got the right results
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.
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:in this case you can also use an
IN
query: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 justNOT IN
I think my query is quite a bit easier than WINDOWing and CTE:
SELECT w1.*
FROM day1.wildfire w1
INNER JOIN ( SELECT cause, MAX(acres) as max_acres FROM day1.wildfire GROUP BY cause ) w2
ON w1.cause = w2.cause AND w1.acres = w2.max_acres;
Nice! I think of window functions as the heavy artillery... not always necessary when a group by might do the job.
Very well explained and helpful!
Thanks!
The database didnt work for me
Is that still work ?