Skip to content

SteveStedman

Transact SQL OVER Clause – ROWS UNBOUNDED PRECEDING or FOLLOWING

In a previous article I covered the usage of ROWS PRECEDING and FOLLOWING in the over clause.  For this example I am going to use the same database and tables that I created in the previous example to show ROWS UNBOUNDED both PRECEEDING and FOLLOWING.

One of the new features available in TSQL in SQL Server 2012 is the ROWS UNBOUNDED PRECEDING and the ROWS UNBOUNDED FOLLOWING options.  The UNBOUNDED option in available when using the PRECEDING and FOLLOWING options.  Here’s how they work…

ROWS UNBOUNDED PRECEDING


-- ROWS UNBOUNDED PRECEDING
select Year, DepartmentID, Revenue,
 min(Revenue) OVER (PARTITION by DepartmentID
 ORDER BY [YEAR]
 ROWS UNBOUNDED PRECEDING) as MinRevenueToDate
from REVENUE
order by departmentID, year;

In this example, the MinRevenueToDate lists the lowest revenue for this row and all earlier rows ordered by date in the current department id.

You can see that row 1 sets the MinRevenueToDate to 10030, and it doesn’t change until row 7 with a lower revenue year.

Read More »Transact SQL OVER Clause – ROWS UNBOUNDED PRECEDING or FOLLOWING