[frankmcsherry]

> If SQL had a way of picking one row from a group, rather than aggregating over it, that would be immensely useful.

You can do this with a LATERAL join, if you want to avoid the jankiness of window functions. Lateral joins are just a programmatic way to introduce a correlated subquery. For example

    SELECT department, first_name, gross_salary FROM
        (SELECT DISTINCT department FROM salary) depts,
        LATERAL (
            SELECT first_name, gross_salary 
            FROM salary 
            WHERE department = depts.department 
            ORDER BY gross_salary DESC
            LIMIT 3
        )

This uses a limit of 3 to show off top-3 instead of just argmax, but you could clearly set that to one if you wanted. This construction can be pretty handy if you need the per-group rows to be something other than what a window function could handle.