Usage of Keep in Oracle __oracle

See a lot of people forKeepDon't understand, here's an explanation.


Returns the row ranked theUsing Dense_rank
2 Kinds of values:
Dense_rank the
Dense_rank Last

InKeep(Dense_rank theORDER BY SL) The result set is to take the example of Max and Min.


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 theORDER by SL] Over (partition by ID),
3 Max (MC)Keep(Dense_rank LastORDER 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>

Don't confuseKeepWithin the、 Last) Outside (min, Max, or others):
Min is able to correspond LastOf
Max is able to correspond theOf

Sql> Select Id,mc,sl,
2 min (MC)Keep(Dense_rank theORDER by SL] Over (partition by ID),
3 Max (MC)Keep(Dense_rank theORDER by SL] Over (partition by ID),
4 min (MC)Keep(Dense_rank LastORDER by SL] Over (partition by ID),
5 Max (MC)Keep(Dense_rank LastORDER 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 theORDER by SL] Over (partition by ID),
3 Max (MC)Keep(Dense_rank theORDER by SL] Over (partition by ID),
4 min (MC)Keep(Dense_rank LastORDER by SL] Over (partition by ID),
5 Max (MC)Keep(Dense_rank LastORDER 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 theORDER BY SL-(partition by ID): the smallest number of IDs equal to 1 (dense_rank the) is
1 111 1
1 222 1
To take Min (MC) In this result is 111
Max (MC)Keep(Dense_rank theORDER by SL] Over (partition by ID)
Take Max (MC) is 222;
Min (MC)Keep(Dense_rank LastORDER BY SL (partition by ID): The largest number of IDs equal to 1 (dense_rank the) is
1 555 3
1 666 3
In this result take min (MC) is 222, take Max (MC) is 666