Oracle SQL - Analytical Function Execution Order


There is an order in which the parts of a SQL statement are processed. For example, a statement that contains:


is executed by the database engine by scanning a table, x, and retrieving rows when the WHERE clause is true.

WHERE is often called a "row filter."

The SELECT .. FROM .. WHERE may contain joins and GROUP BY as well as WHERE.

If there were GROUPING and HAVING clauses, then the criteria in HAVING would be applied after the result of the SELECT .. WHERE is completed.

HAVING is often called an "after filter" because it is done after the other parts of the query are completed.

It is after the initial retrieval which might include joins, after the WHERE, and after the GROUP BY is executed.

If there is ordering in the statement (ORDER BY), the ordering is done last, after the result set has been established from SELECT .. FROM .. WHERE .. HAVING.

The execution process of the analytical function is performed before the ORDER BY.

All grouping, joins, WHERE clauses, and HAVING clauses will have already been applied.

The RANK function is executed between the GROUP BY and the ORDER BY.

Finally, if a HAVING clause is added, it will have its effect just before the RANK.

Related Topic