Analytic functions
Analytic functions were introduced quite a long time ago—Oracle 8.1.6 Enterprise Edition in around 1999—yet they are still quite unknown to a lot of developers.
With analytic functions you can retrieve data from more than one row at the same time without the need for a self join. You can create a ranking based on a value within a group of values. They are not easy to use, but once mastered, analytic functions can make your life a lot easier. With analytic functions, you can create the overviews that the customer may want within a few lines of code.
Syntax overview
The processing order of a query with analytic functions happens in three stages. First of all the Joins, WHERE conditions, GROUP BY, and HAVING clauses are applied. Next, the analytic functions are applied to the resulting result set. Finally, the ORDER BY clause is processed. This order of processing is important to know, because after getting comfortable with analytic functions, it is quite easy to get carried...
