Lesson 29 of 30

Window functions

Today at the studio

The studio awards ceremony is coming, and every category needs a ranking, a running total or a "compared with last time". Aggregates collapse the rows, but this time you need to keep them.

The idea

A window function calculates over a set of rows but keeps every row. You write it with OVER (...). RANK() OVER (ORDER BY salary DESC) gives each row its rank, with ties sharing a rank.

PARTITION BY starts the calculation again for each group. SUM(x) OVER (ORDER BY n) is a running total, and LAG(x) OVER (ORDER BY n) is the value of the previous row, or NULL for the first.

Example

SELECT name, floor,
  ROW_NUMBER() OVER (ORDER BY floor, name) AS position
FROM departments

Your turn

0 of 4 questions

Question 1 of 4

Rank everyone by salary, 1 being the highest. Show name, salary and the rank, sorted by rank and then name.

The order of the rows counts in this question.