SQL - Window Functions

Overview

Window functions compute across related rows without collapsing them into a single row per group.

SELECT first_name, salary,
  ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn,
  LAG(salary, 1) OVER (PARTITION BY department_id ORDER BY salary) AS prev_salary
FROM employees;

SELECT first_name, salary,
  ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn,
  LAG(salary, 1) OVER (PARTITION BY department_id ORDER BY salary) AS prev_salary
FROM employees;

-- SQLite supports many window functions (3.25+)
SELECT first_name, salary,
  ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn,
  LAG(salary, 1) OVER (PARTITION BY department_id ORDER BY salary) AS prev_salary
FROM employees;

« Prev: SQL - GROUP BY & HAVING Next: SQL - UPDATE »