Sort In Original Order When Grouping

Sort In Original Order When Grouping

I am working on analysis against the Soccer World Cup in Brazil and have one small problem; for some reason a handful of event actions have been recorded against multiple dates. It could be a timezone or midnight thing, but it is what it is. Consider the following diagram:

group_by_order_by

This is a list of World Cup games in order of date played. Germany v Portugal, for some reason, has data against both the 16th and 17th – which will stuff me up as I now intend to derive an incremental match number for each game.

Essentially, I need to group by parenteventname – but when I do, the ordering is set to parenteventname. I need to group on parenteventname but maintain the ordering as it is here.

It is surprisingly simple in SQL, having thought I could get away with this via ROW_NUMBER or DENSE_RANK, but no:

SELECT parenteventname 
FROM #matches 
GROUP BY parenteventname 
ORDER BY MIN(id)

I hope this helps!

Mike250

I'm an Australian Chief Analytics Officer passionate about data science, visual insights, and all things sportโ€”particularly cricket. An adventurer at heart, Iโ€™ve gone from abseiling cliffs to snorkeling in crystal-clear waters, sleeping in the wilds of Africa, and exploring destinations worldwide, with my latest trip taking me to Bali. When I'm not diving into data or analytics, I'm spending time with my three sons and two daughters, attempting to hit sixes for my local cricket club, reviewing chicken schnitzels or honing my craft around a coffee machine.

Related Posts
Leave a comment

Your email address will not be published. Required fields are marked *