Recipe
Window Functions
GROUP BY collapses rows into one per group. A window function keeps every row, but still lets it look at the rest of its group: OVER (PARTITION BY ...) defines that group, and RANK() gives each row its position within it.
Query
SELECT name, station, price,
RANK() OVER (PARTITION BY station ORDER BY price DESC) AS price_rank
FROM dishes
ORDER BY station, price_rank;↓ result
name station price price_rank
Bifteki grill 18 1
Souvlaki grill 11 2
Loukoumades pastry 9 1
Baklava pastry 7 2
Melitzanosalata salad 16 1
Dakos salad 14 2Leave out PARTITION BY and the window covers every row at once. Combined with SUM() OVER (ORDER BY ...), that gives a running total instead of one grand total.
Window functions can't appear in WHERE directly (they run after filtering), so to keep only the top-ranked row per group, wrap the ranking in a CTE first and filter the outer query on that.
Query
WITH ranked AS (
SELECT name, station, price,
ROW_NUMBER() OVER (PARTITION BY station ORDER BY price DESC) AS rn
FROM dishes
)
SELECT station, name, price
FROM ranked
WHERE rn = 1
ORDER BY station;↓ result
station name price
grill Bifteki 18
pastry Loukoumades 9
salad Melitzanosalata 16