How Does the `KEEP` Clause Affect `PARTITION BY` Aggregate Functions in Oracle?

PARTITION BY with and without KEEP in Oracle

The PARTITION BY clause in Oracle allows users to group rows in a table based on specified columns for aggregate calculations. In some cases, the KEEP clause can be used in conjunction with PARTITION BY to control the retention of individual rows within each partition.

Syntax with KEEP:

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

In this syntax, the KEEP clause is followed by DENSE_RANK FIRST, indicating that the row with the lowest salary should be kept within each partition. DENSE_RANK ensures continuous ranking, meaning rows with identical salaries will have the same rank.

Syntax without KEEP:

MIN(sal) OVER (PARTITION BY deptno)

In this syntax, the KEEP clause is omitted. Without KEEP, all rows within each partition are considered for the aggregate calculation, which can lead to multiple rows with identical values being included in the result.

Difference:

The main difference between the two queries is that:

• With KEEP: Only the row with the lowest salary within each department is included in the result.
• Without KEEP: All rows within each department are included in the result, potentially leading to duplicate minimum salary values.

Redundant KEEP:

In the provided example, the KEEP clause is redundant because the MIN function is also applied to the same column (sal). As a result, the first ranked row based on salary will always be the one with the lowest salary.

Example with Different Ordering Column:

However, if a different ordering column is used for the KEEP clause, it can have a different effect:

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

In this case, the row with the lowest salary among those with the same ordering (name) will be kept within each partition. This allows users to select the lowest salary for employees with specific names within each department.

The above is the detailed content of How Does the `KEEP` Clause Affect `PARTITION BY` Aggregate Functions in Oracle?. For more information, please follow other related articles on the PHP Chinese website!