For a while, GROUP BY was basically the ceiling of what I knew how to do with aggregation. Total per city, count per venue, that kind of thing. Then I ran into a task — rank venues within their own city by latitude, but keep every single venue row in the result, not collapse them into one row per city — and GROUP BY just couldn't do it. That's the gap window functions fill, and once it clicked, a bunch of queries that used to feel awkward got a lot simpler.

What GROUP BY can't do

GROUP BY collapses rows. If you group venues by city and count them, you get one row per city — the individual venues are gone from the result, folded into that count. That's exactly what you want sometimes.…