This post is part of the series discussing the new Analytic functions in SQL Server “Denali”, CTP3.

Analytic Functions in SQL Server “Denali”

All of the new Analytic functions require the use of the OVER clause. To see what changes to the OVER clause that “Denali” brings, please view my article The OVER Clause enhancements in SQL Server code named “Denali”, CTP3 on SQLServerCentral.com.

Introducing the FIRST_VALUE / LAST_VALUE functions

The FIRST_VALUE and LAST_VALUE functions return the first or last value in the ordered set of your partition, optionally applying a windowing clause to limit the rows being considered.

The syntax of these functions is:

Note that the order_by_clause is required, and the partition_by_clause and the row_range_clause are optional.

The scalar_expression is the value to be returned – this will normally be a column, but it can also be a subquery or any other expression that results in a single value.

Using the FIRST_VALUE and LAST_VALUE functions

For this example, I’m going to use the AdventureWorks for Denali database, which can be downloaded here. In this database, there is a Sales.SalesOrderHeader table, which has information about each order. This information includes CustomerID, OrderDate, and TotalDue columns.

Let’s run a query that shows, for each CustomerID, the OrderDate for when they placed their least and most expensive orders. What this entails is using the FIRST_VALUE and LAST_VALUE functions to return the OrderDate column. The OVER clause specifies that the PARTITION BY is the CustomerID, the ORDER BY is the TotalDue, and the ROWS clause specifies all rows for that CustomerID. The resulting code is:

This code produces the following result set:

By repeating the use of the function, with the same OVER clause definition, we can get other columns from those same rows.

How about another example?

In my OVER clause enhancements article linked to above on SSC, there is an example of performing a running total. To demonstrate the LAST_VALUE function, I’m going to enhance that code so that it not only shows the running total, but to also show the percent of total that each transaction amount is per account. This will require getting the last running total value of the TranAmt column for each account.

The code to create the sample data is (copied from the above article):

and the code to perform the running totals and percent of total is:

The highlighted lines show the LAST_VALUE function. The OVER clause is utilizing all three parts: the PARTITION BY is to restart the calculation for each AccountId, the ORDER BY is on TranDate to ensure that I get the proper row for the last value, and the ROWS clause is utilized in order to get the last value from all of the rows for this account – if it hadn’t had been specified, it would have used the default RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT_ROW, and that would have every row’s percent being 100% – not what we want!

This code produces this result set:

As you can see, the PercentOfTotal column calculates the percentage between the current row’s TranAmt and the TranAmt total for that account.

Hopefully, by seeing some examples, you now have some ideas of how you can utilize these new functions.

References:

FIRST_VALUE
LAST_VALUE

Analytic Function Series:

Overview
LAG/LEAD