SQL Server 2012 introduces new analytical functions FIRST_VALUE() and LAST_VALUE(). This function returns first and last value from the list. It will be very difficult to explain this in words so I’d like to attempt to explain its function through a brief example. Instead of creating a new table, I will be using the AdventureWorks sample database as most developers use that for experiment purposes.
Now let’s have fun following query:
USE AdventureWorks
GO
SELECT s.SalesOrderID,s.SalesOrderDetailID,s.OrderQty,
FIRST_VALUE(SalesOrderDetailID) OVER (ORDER BY SalesOrderDetailID) FstValue,
LAST_VALUE(SalesOrderDetailID) OVER (ORDER BY SalesOrderDetailID) LstValue
FROM Sales.SalesOrderDetail s
WHERE SalesOrderID IN (43670, 43669, 43667, 43663)
ORDER BY s.SalesOrderID,s.SalesOrderDetailID,s.OrderQty
GO
The above query will give us the following result:
What’s the most interesting thing here is that as we go from row 1 to row 10, the value of the FIRST_VALUE() remains the same but the value of the LAST_VALUE is increasing. The reason behind this is that as we progress in every line – considering that line and all the other lines before it, the last value will be of the row where we are currently looking at. To fully understand this statement, see the following figure:
This may be useful in some cases; but not always. However, when we use the same thing with PARTITION BY, the same query starts showing the result which can be easily used in analytical algorithms and needs.
Let us have fun through the following query:
Let us fun following query.
USE AdventureWorks
GO
SELECT s.SalesOrderID,s.SalesOrderDetailID,s.OrderQty,
FIRST_VALUE(SalesOrderDetailID) OVER (PARTITION BY SalesOrderID
ORDER BY SalesOrderDetailID) FstValue,
LAST_VALUE(SalesOrderDetailID) OVER (PARTITION BY SalesOrderID
ORDER BY SalesOrderDetailID) LstValue
FROM Sales.SalesOrderDetail s
WHERE SalesOrderID IN (43670, 43669, 43667, 43663)
ORDER BY s.SalesOrderID,s.SalesOrderDetailID,s.OrderQty
GO
The above query will give us the following result:
Let us understand how PARTITION BY windows the resultset.
I have used PARTITION BY SalesOrderID in my query. This will create small windows of the resultset from the original resultset and will follow the logic or FIRST_VALUE and LAST_VALUE in this resultset.
Well, this is just an introduction to these functions. In the future blog posts we will go deeper to discuss the usage of these two functions. By the way, these functions can be applied over VARCHAR fields as well and are not limited to the numeric field only.
Reference: Pinal Dave (
http://blog.SQLAuthority.com
)
Very nicely explained. Thank you Pinal. Much appreciated.
Awesome sir. Excited to see more of the new features..
Nice feature..
Fine Pinal, As you mentioned this may be useful in some cases, I’m interested to know if this feature is useful in real time any where. Can you or any one give a nice real time scenario where we can use this feature.
Good stuff Pinal. Thanks!
This might be handy, for a project I’m working on. In this project I got a view that needs to calculate how a quota’s evolution compares to what is actually being produced. Since a quota can complete a goal the view needs to take this into account.
Super. please keep posting
This is similar to First.ID and Last.ID in SAS…
Pingback: SQL SERVER – OVER clause with FIRST _VALUE and LAST_VALUE – Analytic Functions Introduced in SQL Server 2012 – ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING « Journey to SQLAuthority
I think these functions can come in handy specially for reporting purposes. You would typically end up writing a correlated sub query or a cte joining to itself to achieve the above result. And first_value, last_value will eliminate them. It would be interesting to see the performance benefits of these new boys!
Very nice pinal
Pingback: SQL SERVER – Puzzle to Win Print Book – Functions FIRST_VALUE and LAST_VALUE with OVER clause and ORDER BY « Journey to SQLAuthority
Hi Pinal,
It’s very good article but please more elaborate it various data types so that we are easily understand and apply it.
Isn’t this the same functionality they have had in MS Access queries for years with First() and Last()?
Thanks, Pinal, in some cases it will be an useful function. MS Access First() and Last() are grouping functions, I suppose , this seems to be a row-level function
Nice to learn the new features in 2012
Pingback: SQL SERVER – Beginning New Weekly Series – Memory Lane – #002 « SQL Server Journey with SQL Authority
LOL the First() function which is doing the same is existing in Microsoft ACCESS for 15 years !!!
Yes ACCESS is a different thing :)
Pingback: SQL SERVER – Weekly Series – Memory Lane – #003 « SQL Server Journey with SQL Authority
Pingback: SQL SERVER – Weekly Series – Memory Lane – #007 « SQL Server Journey with SQL Authority
Is there a function like SECOND_VALUE() for selecting second value per group?
I mean by using only analytical window function (not ranking)
Very nice explanation. Thanks
how about pre2012? lets say server 2008…
convert this access code to server08. the first function doesnt work
SELECT DISTINCT LGCUSTOMER.CUST_CODE, LGCUSTOMER.CUST_FNAME, LGCUSTOMER.CUST_LNAME, LGCUSTOMER.CUST_STREET, LGCUSTOMER.CUST_CITY, LGCUSTOMER.CUST_STATE, LGCUSTOMER.CUST_ZIP, First(LGINVOICE.INV_DATE) AS INV_DATE, Max(LGINVOICE.INV_TOTAL) AS [Largest Invoice]
FROM LGCUSTOMER LEFT JOIN LGINVOICE ON LGCUSTOMER.CUST_CODE = LGINVOICE.CUST_CODE
GROUP BY LGCUSTOMER.CUST_CODE, LGCUSTOMER.CUST_FNAME, LGCUSTOMER.CUST_LNAME, LGCUSTOMER.CUST_STREET, LGCUSTOMER.CUST_CITY, LGCUSTOMER.CUST_STATE, LGCUSTOMER.CUST_ZIP
HAVING (((LGCUSTOMER.CUST_STATE)=”AL”))
ORDER BY LGCUSTOMER.CUST_LNAME;
Would be great to have it produce a table that has the first value and last value in across all rows. Only First value replicated down and last value you have to hunt for it.
Would anyone know if there is a way to simulate this function (specifically last) in SQL Server 2008? Thinking along the lines of this… http://blog.sqlauthority.com/2011/11/24/sql-server-solution-to-puzzle-simulate-lead-and-lag-without-using-sql-server-2012-analytic-function/