The OVER AND ORDER BY window functions are very useful when it comes to ranking within a subset of rows, calculating running totals or simply avoiding self-joins.
To illustrate when to use this most useful feature, we will take the previous example and determine the best paid employees on each payroll per manager by executing this query:
SELECT id, last_name, salary, superior AS manager_id, (SELECT last_name FROM employees WHERE id = manager_id) AS manager_last_name, SUM(salary) OVER(PARTITION BY superior ORDER BY manager_last_name, salary DESC, id) AS payroll_per_managerFROM employeesWHERE superior IS NOT NULLORDER BY manager_last_name, salary DESC, id;
Executing this query will give this result set:
List of the best paid ...
