Analytische Funktionen - Beispiele

Ermitteln der Werte, die über einem Durchschnittswert liegen:

WITH 
   dept_costs AS (
      SELECT department_name, SUM(salary) dept_total
         FROM employees e, departments d
         WHERE e.department_id = d.department_id
      GROUP BY department_name),
   avg_cost AS (
      SELECT SUM(dept_total)/COUNT(*) avg
      FROM dept_costs)
SELECT * FROM dept_costs
   WHERE dept_total >
      (SELECT avg FROM avg_cost)

Ermitteln eines Maximalwertes:

SELECT deptno
     , ename
     , hiredate
     , max(hiredate) over (partition by deptno) last_date
  FROM emp
;

[siehe: http://sql-plsql-de.blogspot.de/2008/05/den-jngsten-datensatz-selektieren-mit.html]