How Does Oracle's `PARTITION BY` Clause Differ with and without `KEEP`?

PARTITION BY with and without KEEP in Oracle

Introduction

In Oracle, the PARTITION BY clause is used to divide a set of data into subsets for performing aggregate calculations. The KEEP clause specifies which rows to keep from each partition. This article discusses the differences between using PARTITION BY with and without KEEP.

Queries with and without KEEP

Consider the following two queries:

SELECT
    empno,
    deptno,
    sal,
    MIN(sal) OVER (PARTITION BY deptno) AS "Lowest",
    MAX(sal) OVER (PARTITION BY deptno) AS "Highest"
FROM
    empl;

SELECT
    empno,
    deptno,
    sal,
    MIN(sal) KEEP (DENSE_RANK FIRST ORDER BY sal) OVER (PARTITION BY deptno) AS "Lowest",
    MAX(sal) KEEP (DENSE_RANK LAST ORDER BY sal) OVER (PARTITION BY deptno) AS "Highest"
FROM
    empl;

The first query uses PARTITION BY without KEEP, while the second query uses KEEP (DENSE_RANK). Both queries calculate the lowest and highest salaries for each department.

KEEP (DENSE_RANK FIRST ORDER BY sal)

In the second query, the KEEP (DENSE_RANK FIRST ORDER BY sal) clause instructs Oracle to keep only the first row for each department after sorting the rows by salary in ascending order. The DENSE_RANK function assigns a rank to each row within each partition, and the first rank indicates the lowest salary.

Difference between the Queries

The second query is redundant because the MIN and DENSE_RANK FIRST both operate on the same sal column. They will produce the same result as the first query, which calculates the lowest salary directly without using KEEP.

This is because the KEEP (DENSE_RANK FIRST ORDER BY sal) clause is only useful when the MIN or MAX aggregation is performed on a different column from the ordering column. In such cases, the KEEP clause allows for more specific control over which rows are included in the calculation.

Example

Consider the following query:

SELECT
    name,
    sal,
    deptno,
    MIN(sal) KEEP (DENSE_RANK FIRST ORDER BY sal) OVER (PARTITION BY deptno) AS "Lowest",
    MIN(name) KEEP (DENSE_RANK FIRST ORDER BY sal) OVER (PARTITION BY deptno) AS "Lowest Employee"
FROM
    test;

This query calculates the lowest salary for each department, along with the name of the employee with the lowest salary. The KEEP (DENSE_RANK FIRST ORDER BY sal) clause is necessary in this case because it ensures that only the lowest salary and corresponding employee name are returned, even if there are multiple rows with the same lowest salary.

The above is the detailed content of How Does Oracle's `PARTITION BY` Clause Differ with and without `KEEP`?. For more information, please follow other related articles on the PHP Chinese website!