Today's question (by Steve Jones - SSC Editor):

Which Thursday is the Holiday?

Think you know the answer? Click here, and find out if you are right.

Further Defining the Window Clause

I have defined a window in SQL Server 2022 as follows:

SELECT 
SUM(sod.OrderQty) OVER() AS Total
 FROM Sales.SalesOrderDetail AS sod
 WHERE sod.ProductID IN (710, 711, 712)
 WINDOW ob_OrderID_ProductID AS (ORDER BY sod.SalesOrderID, sod.ProductID)

In the SUM() aggregate, I want to partition the data. Which of these statements is the way to make a more detailed window over() clause?

A:

SELECT 
SUM(sod.OrderQty) OVER(ob_OrderID_ProductID partition BY sod.SalesOrderID) AS Total
 FROM Sales.SalesOrderDetail AS sod
 WHERE sod.ProductID IN (710, 711, 712)
 WINDOW ob_OrderID_ProductID AS (ORDER BY sod.SalesOrderID, sod.ProductID)

B:

SELECT 
SUM(sod.OrderQty) OVER(ob_OrderID_ProductID partition BY sod.SalesOrderID) AS Total
 FROM Sales.SalesOrderDetail AS sod
 WHERE sod.ProductID IN (710, 711, 712)
 WINDOW ob_OrderID_ProductID AS (ORDER BY sod.SalesOrderID, sod.ProductID), 
        pb_SalesOrderID_ob_OrderID_ProductID AS (PARTITION BY sod.SalesOrderID ORDER BY sod.SalesOrderID, sod.ProductID)

C:

SELECT 
SUM(sod.OrderQty) OVER(partition BY sod.SalesOrderID ORDER BY ob_OrderID_ProductID) AS Total
 FROM Sales.SalesOrderDetail AS sod
 WHERE sod.ProductID IN (710, 711, 712)
 WINDOW ob_OrderID_ProductID AS (ORDER BY sod.SalesOrderID, sod.ProductID)

D:

SELECT 
SUM(sod.OrderQty) OVER(partition BY pb_SalesOrderID ORDER BY ob_OrderID_ProductID) AS Total
 FROM Sales.SalesOrderDetail AS sod
 WHERE sod.ProductID IN (710, 711, 712)
 WINDOW ob_OrderID_ProductID AS (sod.SalesOrderID, sod.ProductID),
        pb_SalesOrderID AS (sod.SalesOrderID)

Answer: A

Explanation: A is the correct syntax. Once a window is defined, any parts not included use the defaults. However, after using the window name in the OVER() clause, you can define additional overrides for the defaults. Ref: SELECT - WINDOW - https://learn.microsoft.com/en-us/sql/t-sql/queries/select-window-transact-sql?view=sql-server-ver16

Discuss this question and answer on the forums

Here's a few of the new posts today on the forums. To see more, visit the forums.