Explanation of KEEP in Oracle FIRST/LAST

Question

Question

Please explain what KEEP exactly is and the effect of with/without it.

Looking for an explanation but could not find a clear explanation.

PARTITION BY with and without KEEP in Oracle

The real point/power of "KEEP" is when you aggregate and sort on different columns.

Keep Clause

Unfortunately, when you start searching for the "keep" clause, you won't find anything in the Oracle documentation (and hopefully because of this blogpost, people will now have a reference). Of course Oracle documents such functions. You only have to know that they are called FIRST and LAST in the SQL Language Reference.

# However, you can do even better by just adding three "keep clause" functions to the original query:
SELECT
    ra.relation_id,
    MAX(ra.startdate) startdate,
    MAX(ra.address) KEEP(DENSE_RANK LAST ORDER BY ra.startdate) address,
    MAX(ra.postal_code) KEEP(DENSE_RANK LAST ORDER BY ra.startdate) postal_code,
    MAX(ra.city) KEEP(DENSE_RANK LAST ORDER BY ra.startdate) city
FROM
    relation_addresses ra
WHERE
    ra.startdate <= to_date(:reference_date, 'yyyy-mm-dd')
GROUP BY
    ra.relation_id

Answer 1

Paraphrasing my answer here:

MAX(ra.address) KEEP(DENSE_RANK LAST ORDER BY ra.startdate)

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

• ORDER BY ra.startdate means order the rows, within each group, by the startdate column of the ra aliased table (implicitly using ASCending order); then
• KEEP (DENSE_RANK LAST means give a (consecutive) ranking to those ordered rows within each group (rows with identical values for the ordering columns will be given the same rank) and KEEP only those rows that are LAST in the ranking (i.e. the rows with the maximum startdate); and finally
• MAX(ra.address) for the remaining kept rows of each group, return the maximum address.

You are finding the maximum address value out of the rows with the maximum (latest) startdate for each group.

---

Without the KEEP clause:

MAX(ra.startdate)

Means find the maximum (latest) of the startdate column for each group.