PARTITION BY with and without KEEP in Oracle

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).

MIN(sal) KEEP (DENSE_RANK FIRST ORDER BY sal) OVER (PARTITION BY deptno)

The statement can be considered in (roughly) right-to-left order:

• OVER (PARTITION BY deptno) means partition the rows into distinct groups of deptno; then
• ORDER BY sal means, for each partition, order the rows by sal (implicitly using ASCending order); then
• KEEP (DENSE_RANK FIRST means give a (consecutive) ranking to the ordered rows for each partition (rows with identical values for the ordering columns will be given the same rank) and discard all rows which are not ranked first; and finally
• MIN(sal) for the remaining rows of each partition, return the minimum salary.

In this case the MIN and DENSE_RANK FIRST are both operating on the sal column so will do the same thing and the KEEP (DENSE_RANK FIRST ORDER BY sal) is redundant.

However if you use a different column for the minimum then you can see the effects:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE test (name, sal, deptno) AS
SELECT 'a', 1, 1 FROM DUAL
UNION ALL SELECT 'b', 1, 1 FROM DUAL
UNION ALL SELECT 'c', 1, 1 FROM DUAL
UNION ALL SELECT 'd', 2, 1 FROM DUAL
UNION ALL SELECT 'e', 3, 1 FROM DUAL
UNION ALL SELECT 'f', 3, 1 FROM DUAL
UNION ALL SELECT 'g', 4, 2 FROM DUAL
UNION ALL SELECT 'h', 4, 2 FROM DUAL
UNION ALL SELECT 'i', 5, 2 FROM DUAL
UNION ALL SELECT 'j', 5, 2 FROM DUAL;

Query 1:

SELECT DISTINCT
  MIN(sal) KEEP (DENSE_RANK FIRST ORDER BY sal) OVER (PARTITION BY deptno) AS min_sal_first_sal,
  MAX(sal) KEEP (DENSE_RANK FIRST ORDER BY sal) OVER (PARTITION BY deptno) AS max_sal_first_sal,
  MIN(name) KEEP (DENSE_RANK FIRST ORDER BY sal) OVER (PARTITION BY deptno) AS min_name_first_sal,
  MAX(name) KEEP (DENSE_RANK FIRST ORDER BY sal) OVER (PARTITION BY deptno) AS max_name_first_sal,
  MIN(name) KEEP (DENSE_RANK LAST ORDER BY sal) OVER (PARTITION BY deptno) AS min_name_last_sal,
  MAX(name) KEEP (DENSE_RANK LAST ORDER BY sal) OVER (PARTITION BY deptno) AS max_name_last_sal,
  deptno
FROM test

Results:

| MIN_SAL_FIRST_SAL | MAX_SAL_FIRST_SAL | MIN_NAME_FIRST_SAL | MAX_NAME_FIRST_SAL | MIN_NAME_LAST_SAL | MAX_NAME_LAST_SAL | DEPTNO |
|-------------------|-------------------|--------------------|--------------------|-------------------|-------------------|--------|
|                 1 |                 1 |                  a |                  c |                 e |                 f |      1 |
|                 4 |                 4 |                  g |                  h |                 i |                 j |      2 |