Saturday, September 3, 2011

SQL Server Denali: FIRST_VALUE() and LAST_VALUE()

In SQL Server Denali introduced these two analytic function. As the Name suggests First_Value() returns the first value in an ordered set of values and Last_Value() returns the Last value in an ordered set of values.

SELECT Cust_ID, Cust_Name, FIRST_VALUE(Cust_ID) OVER (ORDER BY Cust_ID) AS [First Value] FROM   Cust_Table Result:
Cust_ID   Cust_Name First Value
1               A                  1
2               B                  1
3               C                  1
4               D                  1

SELECT Cust_ID, Cust_Name, LAST_VALUE(Cust_ID) OVER (ORDER BY Cust_ID) AS [Last Value] FROM   Cust_Table 
Cust_ID   Cust_Name Last Value
1               A                  4
2               B                   4
3               C                  4
4               D                  4

No comments:

Post a Comment