MAX function example

Eliminating correlated subqueries

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:

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: