Window functions (introduced in the SQL 2003 standard) allow to calculate grouped values without grouping the overall query: multiple times in a query

SELECT last_name,
       first_name,
       department_id,
       salary,
       rank() over (order by salary DESC) "Overal Rank",
       rank() over (partition by department_id ORDER by salary DESC) "Department Rank"
FROM employees
ORDER BY 1,2;

It also allows to easily calculate running values ("up to that row")

SELECT customer_id, 
       order_date, 
       amount,
       sum(amount) over (partition by customer_id order by order_date) as orders_till_now
       sum(amount) over (partition by customer_id) as total_amount
FROM customer_orders
ORDER BY customer_id, order_date;

Some links:

• The tutorial in the PostgreSQL manual
• Understanding Window Functions by Dimitri Fontaine
• Probably the Coolest SQL Feature by Lukas Eder

Back to the SQL Feature Comparison