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 in the Plan Viewer 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; |
Discuss this page in DocCommentXchange. Send feedback about this page using email. |
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1 |