\nSo, quickly on the heels of the first window clause and last_value() question, came a followup:\n<\/p>\n
\nOK smartie, why does last_value work fine here? I didn't have to change from the default window. What gives?\n<\/p>\n
\nselect SalesPersonID, SalesOrderID,
\n first_value(SalesOrderID) over(partition by SalesPersonID order by SalesPersonID) as FirstOrderForSalesPerson,
\n last_value(SalesOrderID) over(partition by SalesPersonID order by SalesPersonID) as LastOrderForSalesPerson
\nfrom Sales.SalesOrderHeader
\nwhere SalesPersonID IS NOT NULL
\norder by SalesPersonID, SalesOrderID\n<\/p>\n
\nIt's close to the last example, except that we're ordering by SalesPersonID instead of SalesOrderID. And, partitioning by (works like group by except that we keep all the detail rows in each group) SalesPersonID. So why DOES it produce the "right" answer for each SalesPersonID?\n<\/p>\n
\nRemember that the query above is equivalent to:\n<\/p>\n
\nselect SalesPersonID, SalesOrderID,
\n first_value(SalesOrderID) over(partition by SalesPersonID order by SalesPersonID
\n range between unbounded preceding and current row) as FirstOrderForSalesPerson,
\n last_value(SalesOrderID) over(partition by SalesPersonID order by SalesPersonID
\n range between unbounded preceding and current row) as LastOrderForSalesPerson
\nfrom Sales.SalesOrderHeader
\nwhere SalesPersonID IS NOT NULL
\norder by SalesPersonID, SalesOrderID\n<\/p>\n
\nAnd it gets the intuitive "right" answer because of the way the "range" windowing spec handles ties. Since the order by clause specifies "SalesPersonID", all the rows for the same SalesPersonID are ties. The window frame with range includes all rows with the same value (all ties) as the part of the frame. So, in the query above, SalesPersonID X has multiple (tied) rows in the window, range considers all of them.\n<\/p>\n
\nContrast to this, using the "rows" windowing spec, which *doesn't* consider multiple tied rows as part of the window, we're back to our "weird" answer.\n<\/p>\n
\nselect SalesPersonID, SalesOrderID,
\n first_value(SalesOrderID) over(partition by SalesPersonID order by SalesPersonID
\n rows between unbounded preceding and current row) as FirstOrderForSalesPerson,
\n last_value(SalesOrderID) over(partition by SalesPersonID order by SalesPersonID
\n rows between unbounded preceding and current row) as LastOrderForSalesPerson
\nfrom Sales.SalesOrderHeader
\nwhere SalesPersonID IS NOT NULL
\norder by SalesPersonID, SalesOrderID\n<\/p>\n
\nA different way to prove to yourself that RANGE considers ties part of the frame and ROWS doesn't is to use the use the SUM aggregate instead of FIRST\/LAST_VALUE().\n<\/p>\n
\n— all of SumOfTotal for a specific SalesPersonID are equal with RANGE
\n— SumOfTotal is equal for each row, each SalesPersonID
\nselect SalesPersonID, SalesOrderID, TotalDue,
\n sum(TotalDue) over(partition by SalesPersonID order by SalesPersonID) as SumOfTotal
\nfrom Sales.SalesOrderHeader
\nwhere SalesPersonID IS NOT NULL\n<\/p>\n
\n— you get a "running total" when using ROWS
\nselect SalesPersonID, SalesOrderID, TotalDue,
\n sum(TotalDue) over(partition by SalesPersonID order by SalesPersonID
\n rows between unbounded preceding and current row) as SumOfTotal
\nfrom Sales.SalesOrderHeader
\nwhere SalesPersonID IS NOT NULL\n<\/p>\n
\nHowever, notice that the running total isn't necessarily by SalesOrderID becuase without an "order by" in the OVER clause, the spec doesn't guarentee ordering by both columns. Note for example, that in the query above, for SalesPersonID 281, SalesOrderID 48327 comes after 48370 (at least it does on my machine, without additional indexes on SalesOrderHeader table).\n<\/p>\n
\nIf you truly want running total by SalesOrderID, in SalesOrderID order<\/strong>, add SalesOrderID to the "ORDER BY" in the OVER clause. When you add SalesOrderID to ORDER BY, now there are no ties in ordering. So either ROWS or RANGE will do the trick.\n<\/p>\n \n— same answer as next query \n— same answer as previous query, uses RANGE by default \nHope this helps, Bob\n<\/p>\n \n@bobbeauch<\/p>\n","protected":false},"excerpt":{"rendered":" So, quickly on the heels of the first window clause and last_value() question, came a followup: OK smartie, why does last_value work fine here? I didn't have to change from the default window. What gives? select SalesPersonID, SalesOrderID, first_value(SalesOrderID) over(partition by SalesPersonID order by SalesPersonID) as FirstOrderForSalesPerson, last_value(SalesOrderID) over(partition by SalesPersonID order by SalesPersonID) as […]<\/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-541","post","type-post","status-publish","format-standard","hentry","category-sql-server-2012","category-transact-sql"],"yoast_head":"\n
\nselect SalesPersonID, SalesOrderID, TotalDue,
\n sum(TotalDue) over(partition by SalesPersonID order by SalesPersonID, SalesOrderID
\n rows between unbounded preceding and current row) as SumOfTotal
\nfrom Sales.SalesOrderHeader
\nwhere SalesPersonID IS NOT NULL\n<\/p>\n
\nselect SalesPersonID, SalesOrderID, TotalDue,
\n sum(TotalDue) over(partition by SalesPersonID order by SalesPersonID, SalesOrderID) as SumOfTotal
\nfrom Sales.SalesOrderHeader
\nwhere SalesPersonID IS NOT NULL\n<\/p>\n