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.

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

user_1509264 · Answer

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

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.

user_19205563 · Answer

NOTE : Consider the following table Table name - relation_addresses

relation_id startdate address posta _code city

1 20-FEB-2019 2,PVR Park 879 776 Tambaram

1 25-MAY-2020 789, CV Raman nagar 877 876 CV Raman nagar

2 18-JAN-2018 97, Paris corner 699 776 Ch main

3 10-MAR-2021 2/ 2678, Park Road 879 776 Bang

4 12-JUN-2019 89, Teck park 638 776 Tech Park

5 03-SEP-2022 309, Paris corner street 673 776 Ch main

5 17-DEC-2020 77, PVR House 690 960 TN

ra.relation_id - Gives the respective relation_id . For example 1
MAX(ra.startdate) startdate - gives Maximum of startdate for relation id 1(25-may-2020)
MAX(ra.address) KEEP(DENSE_RANK LAST ORDER BY ra.startdate) address -
- ORDER BY ra.startdate - Orders the start_date in ascending order (20-FEB-2019, 25-may-2020)
- DENSE_RANK LAST - gives the last startdate (max of startdate) – (25-may-2020)
- MAX(ra.address) KEEP(DENSE_RANK LAST ORDER BY ra.startdate) MAX(ra.address) KEEP(25-may-2020) – address for respective start date - 789, CV Raman nagar
MAX(ra.postal_code) KEEP(DENSE_RANK LAST ORDER BY ra.startdate) postal_code -
- ORDER BY ra.startdate - Orders the start_date in ascending order (20-FEB-2019, 25-may-2020)
- DENSE_RANK LAST - gives the last startdate (max of startdate) – (25-may-2020)
- MAX(ra.postal_code) KEEP(DENSE_RANK LAST ORDER BY ra.startdate) MAX(ra.address) KEEP(25-may-2020) – address for respective start date - 877 876
MAX(ra.city) KEEP(DENSE_RANK LAST ORDER BY ra.startdate) city -
- ORDER BY ra.startdate - Orders the start_date in ascending order (20-FEB-2019, 25-may-2020)
- DENSE_RANK LAST - gives the last startdate (max of startdate) – (25-may-2020)
- MAX(ra.city) KEEP(DENSE_RANK LAST ORDER BY ra.startdate) MAX(ra.address) KEEP(25-may-2020) – address for respective start date - CV Raman nagar

GROUP BY ra.relation_id – output is provided based on group by relation_id

Final output will be