KEEP Klausel

Rob van Wijk, den ich als einen der besten Kenner von Oracles SQL-Repertoire betrachte, erläutert in seinem Blog die Rolle der (zu den Funktionen FIRST und LAST gehörenden) KEEP clause, die die Dokumentation folgendermaßen erklärt:

When you need a value from the first or last row of a sorted group, but the needed value is not the sort key, the FIRST and LAST functions eliminate the need for self-joins or views and enable better performance.

Anders ausgedrückt gestattet es die KEEP clause nach einer anderen Spalte als der Ergebnisspalte zu sortieren. Hier ein simples Beispiel mit EMP, in dem ich pro Department den Mitarbeiter mit der längsten Unternehmenszugehörigkeit anzeigen lasse:

select deptno
     , min(hiredate) hiredate
     , min(ename) keep(dense_rank first order by hiredate) first_ename
     , min(job) keep(dense_rank first order by hiredate) first_job
  from emp
 group by deptno

DEPTNO HIREDATE FIRST_ENAM FIRST_JOB
------ -------- ---------- ---------
    10 09.06.81 CLARK      MANAGER
    20 17.12.80 SMITH      CLERK
    30 20.02.81 ALLEN      SALESMAN

Damit lassen sich unter Umständen überflüssige Self-Joins oder Subqueries mit einer Rank-Analytic vermeiden, also im Beispiel etwa:

with
basedata as (
select t.*
     , row_number() over(partition by t.deptno order by t.hiredate) rn
  from emp t
)
select deptno
     , hiredate
     , ename
     , job
  from basedata
 where rn = 1

DEPTNO HIREDATE ENAME      JOB
------ -------- ---------- ---------
    10 09.06.81 CLARK      MANAGER
    20 17.12.80 SMITH      CLERK
    30 20.02.81 ALLEN      SALESMAN

Der Vorteil der KEEP clause ist dabei nicht nur die kompaktere Syntax, sondern auch eine bessere Performance, was der Herr van Wijk anhand von Beipielen belegt. Neben der Verwendung als Aggregat-Funktionen gibt es übrigens auch eine analytische Variante der Funktionen.