GROUP BY In PostgreSQL

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. It's exactly the problem other times, like when you want to know each venue's rank within its city, but still want every venue listed individually, not just a per-city summary.

Window functions keep every row

SELECT
    name,
    city,
    RANK() OVER (PARTITION BY city ORDER BY latitude DESC) AS rank_in_city
FROM venues;

This returns one row per venue — nothing gets collapsed. Each row also gets a computed value attached to it, rank_in_city, based on how that venue compares to others in the same city. PARTITION BY city is doing something similar to what GROUP BY does conceptually, splitting the data into per-city buckets, but instead of collapsing each bucket into a single summary row, the window function computes something within each bucket and attaches it back onto every original row.

That's really the core difference worth holding onto: GROUP BY reduces row count, window functions don't.

The ones that come up most

ROW_NUMBER(), RANK(), and DENSE_RANK() all number rows within a partition, but they handle ties differently. ROW_NUMBER() gives every row a distinct number regardless of ties, arbitrarily breaking them. RANK() gives tied rows the same number, then skips ahead — two venues tied for rank 1 means the next one is rank 3, not 2. DENSE_RANK() also gives tied rows the same number, but doesn't skip — the next distinct value after a tie at 1 is 2, not 3.

LAG() and LEAD() compare a row to the row before or after it, within the same ordering:

SELECT venue_id, closed_on,
       LAG(closed_on) OVER (PARTITION BY venue_id ORDER BY closed_on) AS previous_closure
FROM closures;

This pulls, for each closure record, the date of that same venue's previous closure, sitting right there in the same row. Useful for things like "days since last closure" style calculations, where you need to compare a row to the one before it without writing a self join to do it.

SUM() OVER (...) gives you running totals — the same idea as a regular SUM, but computed cumulatively per row instead of collapsed into a single number.

Where this actually gets used

A common real request is "top N per category" — for this project, maybe "the two highest-latitude venues in each city." GROUP BY genuinely can't answer this on its own, because it would need to keep multiple rows per city, which defeats the whole point of grouping. The window function version:

SELECT name, city, latitude
FROM (
    SELECT name, city, latitude,
           ROW_NUMBER() OVER (PARTITION BY city ORDER BY latitude DESC) AS rn
    FROM venues
) ranked
WHERE rn <= 2;

The inner query attaches a row number to every venue, restarting at 1 for each new city, ordered by latitude descending. The outer query then just filters down to rn <= 2. This pattern — window function inside, filter outside — is worth recognizing on sight, because "top N per group" comes up often enough that it's basically a template once you've seen it once.

Where this leaves you

Window functions solve a specific gap that GROUP BY leaves open: computing something per group while still keeping every individual row in the result, rather than collapsing down to one row per group. If a query needs a ranking, a comparison to a neighboring row, or a running total, and you catch yourself reaching for GROUP BY and getting frustrated that it's throwing away rows you actually needed, that's usually the signal a window function is what you actually want.

Next up: JSONB in practice — querying and indexing semi-structured data, which comes up constantly once you're syncing data from external sources.