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.

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
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.

Then row 16 starts over for a new department with the lowest running revenue to date.  Then row 21 resets to a lower revenue number.

Now for the example of UNBOUNDED FOLLOWING:


-- ROWS UNBOUNDED FOLLOWING
select Year, DepartmentID, Revenue,
 min(Revenue) OVER (PARTITION by DepartmentID
 ORDER BY [YEAR]
 ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) as MinRevenueBeyond
from REVENUE
order by departmentID, year;

Where you can see that the min(Revenue) is calculated over all of the rows from the current row to the end of the set partitioned by the departmentID.

The UNBOUNDED option is very useful to determine a calculation from the beginning of a set to the current row, or from the current row to the end of a set or group.

Keep in mind that this feature was introduced in SQL Server 2012, and is not available in earlier editions.

3 Responses to Transact SQL OVER Clause – ROWS UNBOUNDED PRECEDING or FOLLOWING

  1. Pingback: » TSQL Analytic Functions LEAD and LAG Steve Stedman

  2. Pingback: » More TSQL 2012 Analytics PERCENTILE_DISC and PERCENTILE_CONT Steve Stedman

  3. Ralf says:

    Hello,
    thanks for the Article.

    I am wondering if this is a breaking change to SQL 2005, 2008 and 2008 R2. The OVER CLAUSE was possible in all these versions, with aggregation operators like SUM, and acted on ALL rows in the window (which was controlled by a PARTITION BY clause if needed)

    As I understand it now with 2012, if I do not specifically put ROWS UNBOUDED in the queries the default behaviour will be UNBOUNDED PRECIDING up to only CURRENT ROW?

    This would be a change to the previous behaviour, right?

    Thanks for your input on this!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Powered by sweetCaptchaWordpress Captcha