Introduction to Window Functions in SQL
What i like most about window functions is that they give me a way to do a sort of 'extended group by' which i have always wanted.<p>If you want to know the highest salary in each department, that's easy:<p><pre><code>  select department, max(gross_salary)
  from salary
  group by department
</code></pre>
If you want to know who it is who earns that salary, you might try to do this:<p><pre><code>  select department, first_name, max(gross_salary)
  from salary
  group by department
</code></pre>
But this doesn't work, because it's meaningless to ask for first_name in a situation where you're grouping by department. You could ask for an aggregation of all names, but there's no straightforward way to ask for the name of the person who earned that salary. You end up having to write a join against the group by, as in the article, which is pretty grim, and falls apart if you want to order by multiple columns to break ties.<p>Window functions let you re-frame this kind of group by like this:<p><pre><code>  select department, gross_salary
  from (
    select *, row_number() over (partition by department order by gross_salary desc) as n
    from salary
  ) _
  where n = 1
</code></pre>
Because the outer query is no longer a group by, you can select any columns you like. The natural query works fine:<p><pre><code>  select department, first_name, gross_salary
  from (
    select *, row_number() over (partition by department order by gross_salary desc) as n
    from salary
  ) _
  where n = 1
</code></pre>
This only works where the group by is based on an aggregate function that picks one value, like min or max. I somewhat think it was a mistake to model that kind of thing as an aggregation in the first place. If SQL had a way of picking one row from a group, rather than aggregating over it, that would be immensely useful.