EXCLUDE Clause

EXCLUDE Clause is a SQL function in the Window Functions category. SQL standard: excludes current row, ties, or group from the window frame. The syntax is SUM(col) OVER (ORDER BY col ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING EXCLUDE CURRENT ROW). It accepts col. It returns exclusion-aware window result. A typical example: SELECT name, salary, SUM(salary) OVER (ORDER BY salary ROWS UNBOUNDED PRECEDING EXCLUDE CURRENT ROW) AS running_total_excl FROM employees; -- Running total excluding the current row's salary -- EXCLUDE TIES: exclude rows with same ORDER BY value -- EXCLUDE GROUP: exclude all peers in the same group A close relative is ROW_NUMBER(), which assigns a unique sequential integer to each row within its partition (1, 2, 3...). A close relative is RANK(), which similar to ROW_NUMBER but gives the same rank to equal values. Leaves gaps in ranking. A close relative is DENSE_RANK(), which like RANK but without gaps. Equal values get the same rank, and the next number follows sequentially.