\nI've been working with the new Denali T-SQL windowing functionality and ran into someone who asked about this "problem". It's almost sure to become an FAQ.\n<\/p>\n
\nWhy does last_value not always "work right"?\n<\/p>\n
\nTake, as an example, the following query (against Adventureworks2008R2)\n<\/p>\n
\nselect AccountNumber, SalesPersonID, SalesOrderID,
\n first_value(SalesOrderID) over(partition by AccountNumber order by SalesOrderID) as FirstOrderForAcct,
\n last_value(SalesOrderID) over(partition by AccountNumber order by SalesOrderID) as LastOrderForAcct
\nfrom Sales.SalesOrderHeader
\nwhere SalesPersonID IS NOT NULL\n<\/p>\n
\nWhen you look as the results, FirstOrderForAcct seems OK, but LastOrderForAcct always returns the same number as the current account for each row. Not the last number. Why?\n<\/p>\n
\nThe reason is that, when using first\/last_value with a window of rows, the default window is "range between unbounded preceding and current row". So the query above with the "weird" result is equivalent to this one.\n<\/p>\n
\nselect AccountNumber, SalesPersonID, SalesOrderID, \nIf you want the "intuitive" answer for last_value, just change the window clause to:\n<\/p>\n \n \n@bobbeauch<\/p>\n","protected":false},"excerpt":{"rendered":" I've been working with the new Denali T-SQL windowing functionality and ran into someone who asked about this "problem". It's almost sure to become an FAQ. Why does last_value not always "work right"? Take, as an example, the following query (against Adventureworks2008R2) select AccountNumber, SalesPersonID, SalesOrderID, first_value(SalesOrderID) over(partition by AccountNumber order by SalesOrderID) as FirstOrderForAcct, […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[31,40],"tags":[],"class_list":["post-542","post","type-post","status-publish","format-standard","hentry","category-sql-server-2012","category-transact-sql"],"yoast_head":"\n
\n first_value(SalesOrderID) over(partition by AccountNumber order by SalesOrderID
\n range between unbounded preceding and current row<\/strong>) as FirstOrderForAcct,
\n last_value(SalesOrderID) over(partition by AccountNumber order by SalesOrderID
\n range between unbounded preceding and current row<\/strong>) as LastOrderForAcct
\nfrom Sales.SalesOrderHeader
\nwhere SalesPersonID IS NOT NULL\n<\/p>\n
\nselect AccountNumber, SalesPersonID, SalesOrderID,
\n first_value(SalesOrderID) over(partition by AccountNumber order by SalesOrderID
\n range between unbounded preceding and unbounded following<\/strong>) as FirstOrderForAcct,
\n last_value(SalesOrderID) over(partition by AccountNumber order by SalesOrderID
\n range between unbounded preceding and unbounded following<\/strong>) as LastOrderForAcct
\nfrom Sales.SalesOrderHeader
\nwhere SalesPersonID IS NOT NULL\n<\/p>\n