SQL Server Window Offset Functions
SQL Server Window Offset Functions is a SQL function in the SQL Server Specific category. SQL Server 2012+: full window function support including frame specifications (ROWS/RANGE). The syntax is FIRST_VALUE / LAST_VALUE / LAG / LEAD — all supported with OVER clause. It accepts see syntax. It returns window function results. A typical example: SELECT name, salary, department, FIRST_VALUE(name) OVER (PARTITION BY department ORDER BY salary DESC) AS highest_paid, LAST_VALUE(name) OVER ( PARTITION BY department ORDER BY salary DESC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS lowest_paid, LAG(salary, 1, 0) OVER (PARTITION BY department ORDER BY salary DESC) AS prev_salary, LEAD(salary, 1, 0) OVER (PARTITION BY department ORDER BY salary DESC) AS next_salary FROM employees; -- Note: LAST_VALUE needs explicit frame to… A close relative is OUTPUT Clause, which sQL Server: returns values from DML statements (like PostgreSQL RETURNING). A close relative is STUFF(), which sQL Server: deletes a specified length of characters from a string and inserts another string at the start position.