PARTITION BY with and without KEEP in Oracle

oracle, sql

Solution

In your example, there's no difference, because your aggregate is on the same column that you are sorting on. The real point/power of "KEEP" is when you aggregate and sort on different columns. For example (borrowing the "test" table from the other answer)...

SELECT deptno,  min(name) keep ( dense_rank first order by sal desc, name  ) ,
max(sal)
FROM test
group by deptno

;

This query gets the name of person with the highest salary in each department. Consider the alternative without a "KEEP" clause:

SELECT deptno, name, sal
FROM test t
WHERE not exists ( SELECT 'person with higher salary in same department'
                                            FROM test t2  
                                            WHERE t2.deptno = t.deptno
                                            and ((  t2.sal > t.sal )
                                            OR ( t2.sal = t.sal AND t2.name < t.name ) ) )

The KEEP clause is easier and more efficient (only 3 consistent gets vs 34 gets for the alternative, in this simple example).

Problem

I came across two queries which seems to have the same result: applying aggregate function on partition. I am wondering if there is any difference between these two queries: ``` SELECT empno, deptno, sal, MIN(sal) OVER (PARTITION BY deptno) "Lowest", MAX(sal) OVER (PARTITION BY deptno) "Highest" FROM empl SELECT empno, deptno, sal, MIN(sal) KEEP (DENSE_RANK FIRST ORDER BY sal) OVER (PARTITION BY deptno) "Lowest", MAX(sal) KEEP (DENSE_RANK LAST ORDER BY sal) OVER (PARTITION BY deptno) "Highest" FROM empl ``` The first version is more logical but second one may be some kind special case, maybe some performance optimization.

Original source