Sql server® 2012 t-sql fundamentals

 

CHAPTER 7

 

Beyond the Fundamentals of Querying

 

 219

with the window frame extent ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. If 

you want the element from the last row in the window partition, use LAST_VALUE with the window 

frame extent ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING. Note that if you 

specify ORDER BY without a window frame unit (such as ROWS), the bottom delimiter will by default 

be CURRENT ROW, and clearly that’s not what you want with LAST_VALUE. Also, for reasons that 

are beyond the scope of this book, you should be explicit about the window frame extent even for 

FIRST_VALUE.

As an example, the following query uses the FIRST_VALUE function to return the value of the first 

customer’s order and the LAST_VALUE function to return the value of the last customer’s order.

SELECT custid, orderid, val, 

  FIRST_VALUE(val) OVER(PARTITION BY custid 

                        ORDER BY orderdate, orderid 

                        ROWS BETWEEN UNBOUNDED PRECEDING 

                                 AND CURRENT ROW) AS firstval, 

  LAST_VALUE(val)  OVER(PARTITION BY custid 

                        ORDER BY orderdate, orderid 

                        ROWS BETWEEN CURRENT ROW 

                                 AND UNBOUNDED FOLLOWING) AS lastval 

FROM Sales.OrderValues 

ORDER BY custid, orderdate, orderid;

This query generates the following output, shown here in abbreviated form.

custid  orderid  val      firstval  lastval 

------- -------- -------- --------- -------- 

1       10643    814.50   814.50    933.50 

1       10692    878.00   814.50    933.50 

1       10702    330.00   814.50    933.50 

1       10835    845.80   814.50    933.50 

1       10952    471.20   814.50    933.50 

1       11011    933.50   814.50    933.50 

2       10308    88.80    88.80     514.40 

2       10625    479.75   88.80     514.40 

2       10759    320.00   88.80     514.40 

2       10926    514.40   88.80     514.40 

3       10365    403.20   403.20    660.00 

3       10507    749.06   403.20    660.00 

3       10535    1940.85  403.20    660.00 

3       10573    2082.00  403.20    660.00 

3       10677    813.37   403.20    660.00 

3       10682    375.50   403.20    660.00 

3       10856    660.00   403.20    660.00 

... 

 

(830 row(s) affected)

As with LAG and LEAD, normally you would compute something based on the returned values. For 

example, you could compute the difference between the current customer’s order value and the first: 

val – FIRST_VALUE(val) OVER(…) or the difference from the last: val – LAST_VALUE(val) OVER(…).

www.it-ebooks.info

---