Function --- keep

Java code
KEEP

Many people do not understand keep. Let's explain it here!


Returns the row ranked first using DENSE_RANK
Two values:
DENSE_RANK FIRST
DENSE_RANK LAST

In the keep (DENSE_RANK first order by sl) result set, the max and min values are obtained.


SQL> select * from test;

ID MC SL
-----------------------------------------------------------
1 111 1
1 222 1
1 333 2
1 555 3
1 666 3
2 111 1
2 222 1
2 333 2
2 555 2

9 rows selected

SQL>
SQL> select id, mc, sl,
2 min (mc) keep (DENSE_RANK first order by sl) over (partition by id ),
3 max (mc) keep (DENSE_RANK last order by sl) over (partition by id)
4 from test
5;

Id mc sl min (MC) KEEP (DENSE_RANKFIRSTORD MAX (MC) KEEP (DENSE_RANKLASTORDE
-----------------------------------------------------------------------------------------------------------------------
1 111 1 111 666
1 222 1 111 666
1 333 2 111 666
1 555 3 111 666
1 666 3 111 666
2 111 1 111 555
2 222 1 111 555
2 333 2 111 555
2 555 2 111 555

9 rows selected

SQL>

Do not confuse (min, max, or others) outside the keep (first, last ):
Min can correspond to last
Max corresponds to first.

SQL> select id, mc, sl,
2 min (mc) keep (DENSE_RANK first order by sl) over (partition by id ),
3 max (mc) keep (DENSE_RANK first order by sl) over (partition by id ),
4 min (mc) keep (DENSE_RANK last order by sl) over (partition by id ),
5 max (mc) keep (DENSE_RANK last order by sl) over (partition by id)
6 from test
7;

Id mc sl min (MC) KEEP (DENSE_RANKFIRSTORD MAX (MC) KEEP (DENSE_RANKFIRSTORD MIN (MC) KEEP (DENSE_RANKLASTORDE MAX (MC) KEEP (DENSE_RANKLASTORDE
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 111 1 111 222 555 666
1 222 1 111 222 555 666
1 333 2 111 222 555 666
1 555 3 111 222 555 666
1 666 3 111 222 555 666
2 111 1 111 222 333 555
2 222 1 111 222 333 555
2 333 2 111 222 333 555
2 555 2 111 222 333 555

9 rows selected


SQL> select id, mc, sl,
2 min (mc) keep (DENSE_RANK first order by sl) over (partition by id ),
3 max (mc) keep (DENSE_RANK first order by sl) over (partition by id ),
4 min (mc) keep (DENSE_RANK last order by sl) over (partition by id ),
5 max (mc) keep (DENSE_RANK last order by sl) over (partition by id)
6 from test
7;

Id mc sl min (MC) KEEP (DENSE_RANKFIRSTORD MAX (MC) KEEP (DENSE_RANKFIRSTORD MIN (MC) KEEP (DENSE_RANKLASTORDE MAX (MC) KEEP (DENSE_RANKLASTORDE
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 111 1 111 222 555 666
1 222 1 111 222 555 666
1 333 2 111 222 555 666
1 555 3 111 222 555 666
1 666 3 111 222 555 666

2 111 1 111 222 333 555
2 222 1 111 222 333 555
2 333 2 111 222 333 555
2 555 2 111 222 333 555

Min (mc) keep (DENSE_RANK first order by sl) over (partition by id): the minimum number of IDS equal to 1 (DENSE_RANK first) is
1 111 1
1 222 1
In this result, min (mc) is 111.
Max (mc) keep (DENSE_RANK first order by sl) over (partition by id)
Max (mc) is 222;
Min (mc) keep (DENSE_RANK last order by sl) over (partition by id): the largest number of IDS equal to 1 (DENSE_RANK first) is
1 555 3
1 666 3
In this result, min (mc) is 222, max (mc) is 666.