|
In some situations, you may need the ability to compare a particular column value with a maximum or minimum value. Often you form these queries as nested queries involving a correlated attribute (also known as an outer reference). As an example, consider the following query, which lists all orders, including product information, where the product quantity-on-hand cannot cover the maximum single order for that product:
SELECT o.ID, o.OrderDate, p.*
FROM SalesOrders o, SalesOrderItems s, Products p
WHERE o.ID = s.ID AND s.ProductID = p.ID
AND p.Quantity < ( SELECT MAX( s2.Quantity )
FROM SalesOrderItems s2
WHERE s2.ProductID = p.ID )
ORDER BY p.ID, o.ID;
The graphical plan for this query is displayed on the Plan tab, in the Results pane of Interactive SQL, as shown below. Note how the query optimizer has transformed this nested query to a join of the Products and SalesOrders tables with a derived table, denoted by the correlation name DT, which contains a window function.
Rather than relying on the optimizer to transform the correlated subquery into a join with a derived table—which can only be done for straightforward cases due to the complexity of the semantic analysis—you can form such queries using a window function:
SELECT order_qty.ID, o.OrderDate, p.*
FROM ( SELECT s.ID, s.ProductID,
MAX( s.Quantity ) OVER (
PARTITION BY s.ProductID
ORDER BY s.ProductID )
AS max_q
FROM SalesOrderItems s )
AS order_qty, Products p, SalesOrders o
WHERE p.ID = ProductID
AND o.ID = order_qty.ID
AND p.Quantity < max_q
ORDER BY p.ID, o.ID;