Analytical Functions Oracle PLSQL

SQL>
SQL> create table t
  2  as
  3  select object_name ename,
  4         mod(object_id,50) deptno,
  5         object_id sal
  6    from all_objects
  7   where rownum <= 1000
  8  /
Table created.
SQL>
SQL>
SQL> select *
  2  from (select deptno, ename, sal,
  3        row_number() over (partition by deptno
  4                           order by sal desc)
  5        rn from t)
  6  where rn <= 3
  7  /
DEPTNO ENAME                               SAL       RN
------ ------------------------------ -------- --------
     0 V_$LOCK                         1050.00     1.00
       V_$BUFFER_POOL_STATISTICS       1000.00     2.00
       V_$DLM_ALL_LOCKS                 950.00     3.00
     1 V$LOCK                          1051.00     1.00
       V$BUFFER_POOL_STATISTICS        1001.00     2.00
       V$DLM_ALL_LOCKS                  951.00     3.00
     2 V_$SESSTAT                      1052.00     1.00
       V_$INSTANCE_RECOVERY            1002.00     2.00
       V_$DLM_LOCKS                     952.00     3.00
DEPTNO ENAME                               SAL       RN
------ ------------------------------ -------- --------
     3 V$SESSTAT                       1053.00     1.00
       V$INSTANCE_RECOVERY             1003.00     2.00
       V$DLM_LOCKS                      953.00     3.00
     4 V_$MYSTAT                       1054.00     1.00
       V_$CONTROLFILE                  1004.00     2.00
       V_$DLM_RESS                      954.00     3.00
     5 V$MYSTAT                        1055.00     1.00
       V$CONTROLFILE                   1005.00     2.00
DEPTNO ENAME                               SAL       RN
------ ------------------------------ -------- --------
     5 V$DLM_RESS                       955.00     3.00
     6 V_$SUBCACHE                     1056.00     1.00
       V_$LOG                          1006.00     2.00
       V_$HVMASTER_INFO                 956.00     3.00
     7 V$SUBCACHE                      1057.00     1.00
       V$LOG                           1007.00     2.00
       V$HVMASTER_INFO                  957.00     3.00
     8 V_$SYSSTAT                      1058.00     1.00
DEPTNO ENAME                               SAL       RN
------ ------------------------------ -------- --------
     8 V_$STANDBY_LOG                  1008.00     2.00
       V_$GCSHVMASTER_INFO              958.00     3.00
     9 V$SYSSTAT                       1059.00     1.00
       V$STANDBY_LOG                   1009.00     2.00
       V$GCSHVMASTER_INFO               959.00     3.00
    10 V_$STATNAME                     1060.00     1.00
       V_$DATAGUARD_STATUS             1010.00     2.00
       V_$GCSPFMASTER_INFO              960.00     3.00
DEPTNO ENAME                               SAL       RN
------ ------------------------------ -------- --------
    11 V$STATNAME                      1061.00     1.00
       V$DATAGUARD_STATUS              1011.00     2.00
       V$GCSPFMASTER_INFO               961.00     3.00
    12 V_$OSSTAT                       1062.00     1.00
       V_$THREAD                       1012.00     2.00
       GV_$DLM_TRAFFIC_CONTROLLER       962.00     3.00
    13 V$OSSTAT                        1063.00     1.00
       V$THREAD                        1013.00     2.00
       GV$DLM_TRAFFIC_CONTROLLER        963.00     3.00
DEPTNO ENAME                               SAL       RN
------ ------------------------------ -------- --------
    14 V_$ACCESS                       1064.00     1.00
       V_$PROCESS                      1014.00     2.00
       V_$DLM_TRAFFIC_CONTROLLER        964.00     3.00
    15 V$ACCESS                        1065.00     1.00
       V$PROCESS                       1015.00     2.00
       V$DLM_TRAFFIC_CONTROLLER         965.00     3.00
    16 V_$OBJECT_DEPENDENCY            1066.00     1.00
       V_$BGPROCESS                    1016.00     2.00
DEPTNO ENAME                               SAL       RN
------ ------------------------------ -------- --------
    16 V_$GES_ENQUEUE                   966.00     3.00
    17 V$OBJECT_DEPENDENCY             1067.00     1.00
       V$BGPROCESS                     1017.00     2.00
       V$GES_ENQUEUE                    967.00     3.00
    18 V_$DBFILE                       1068.00     1.00
       V_$SESSION                      1018.00     2.00
       V_$GES_BLOCKING_ENQUEUE          968.00     3.00
    19 V$DBFILE                        1069.00     1.00
DEPTNO ENAME                               SAL       RN
------ ------------------------------ -------- --------
    19 V$SESSION                       1019.00     2.00
       V$GES_BLOCKING_ENQUEUE           969.00     3.00
    20 V_$FILESTAT                     1070.00     1.00
       V_$LICENSE                      1020.00     2.00
       V_$GC_ELEMENT                    970.00     3.00
    21 V$FILESTAT                      1071.00     1.00
       V$LICENSE                       1021.00     2.00
       V$GC_ELEMENT                     971.00     3.00
DEPTNO ENAME                               SAL       RN
------ ------------------------------ -------- --------
    22 V_$TEMPSTAT                     1072.00     1.00
       V_$TRANSACTION                  1022.00     2.00
       V_$CR_BLOCK_SERVER               972.00     3.00
    23 V$TEMPSTAT                      1073.00     1.00
       V$TRANSACTION                   1023.00     2.00
       V$CR_BLOCK_SERVER                973.00     3.00
    24 V_$LOGFILE                      1074.00     1.00
       V_$BSP                          1024.00     2.00
       V_$CURRENT_BLOCK_SERVER          974.00     3.00
DEPTNO ENAME                               SAL       RN
------ ------------------------------ -------- --------
    25 V$LOGFILE                       1075.00     1.00
       V$BSP                           1025.00     2.00
       V$CURRENT_BLOCK_SERVER           975.00     3.00
    26 V_$FLASHBACK_DATABASE_LOGFILE   1076.00     1.00
       V_$FAST_START_SERVERS           1026.00     2.00
       V_$GC_ELEMENTS_W_COLLISIONS      976.00     3.00
    27 V$FLASHBACK_DATABASE_LOGFILE    1077.00     1.00
       V$FAST_START_SERVERS            1027.00     2.00
DEPTNO ENAME                               SAL       RN
------ ------------------------------ -------- --------
    27 V$GC_ELEMENTS_WITH_COLLISIONS    977.00     3.00
    28 V_$FLASHBACK_DATABASE_LOG       1078.00     1.00
       V_$FAST_START_TRANSACTIONS      1028.00     2.00
       V_$FILE_CACHE_TRANSFER           978.00     3.00
    29 V$FLASHBACK_DATABASE_LOG        1079.00     1.00
       V$FAST_START_TRANSACTIONS       1029.00     2.00
       V$FILE_CACHE_TRANSFER            979.00     3.00
    30 V_$FLASHBACK_DATABASE_STAT      1080.00     1.00
DEPTNO ENAME                               SAL       RN
------ ------------------------------ -------- --------
    30 V_$LOCKED_OBJECT                1030.00     2.00
       V_$TEMP_CACHE_TRANSFER           980.00     3.00
    31 V$FLASHBACK_DATABASE_STAT       1081.00     1.00
       V$LOCKED_OBJECT                 1031.00     2.00
       V$TEMP_CACHE_TRANSFER            981.00     3.00
    32 V_$RESTORE_POINT                1082.00     1.00
       V_$LATCH                        1032.00     2.00
       V_$CLASS_CACHE_TRANSFER          982.00     3.00
DEPTNO ENAME                               SAL       RN
------ ------------------------------ -------- --------
    33 V$RESTORE_POINT                 1083.00     1.00
       V$LATCH                         1033.00     2.00
       V$CLASS_CACHE_TRANSFER           983.00     3.00
    34 V_$ROLLNAME                     1084.00     1.00
       V_$LATCH_CHILDREN               1034.00     2.00
       V_$BH                            984.00     3.00
    35 V$ROLLNAME                      1085.00     1.00
       V$LATCH_CHILDREN                1035.00     2.00
       V$BH                             985.00     3.00
DEPTNO ENAME                               SAL       RN
------ ------------------------------ -------- --------
    36 V_$ROLLSTAT                     1086.00     1.00
       V_$LATCH_PARENT                 1036.00     2.00
       V_$LOCK_ELEMENT                  986.00     3.00
    37 V$ROLLSTAT                      1087.00     1.00
       V$LATCH_PARENT                  1037.00     2.00
       V$LOCK_ELEMENT                   987.00     3.00
    38 V_$UNDOSTAT                     1088.00     1.00
       V_$LATCHNAME                    1038.00     2.00
DEPTNO ENAME                               SAL       RN
------ ------------------------------ -------- --------
    38 V_$LOCKS_WITH_COLLISIONS         988.00     3.00
    39 V$UNDOSTAT                      1089.00     1.00
       V$LATCHNAME                     1039.00     2.00
       V$LOCKS_WITH_COLLISIONS          989.00     3.00
    40 V_$SGA                          1090.00     1.00
       V_$LATCHHOLDER                  1040.00     2.00
       V_$FILE_PING                     990.00     3.00
    41 V$LATCHHOLDER                   1041.00     1.00
DEPTNO ENAME                               SAL       RN
------ ------------------------------ -------- --------
    41 V$FILE_PING                      991.00     2.00
       V$SQL_OPTIMIZER_ENV              941.00     3.00
    42 V_$LATCH_MISSES                 1042.00     1.00
       V_$TEMP_PING                     992.00     2.00
       V_$DLM_MISC                      942.00     3.00
    43 V$LATCH_MISSES                  1043.00     1.00
       V$TEMP_PING                      993.00     2.00
       V$DLM_MISC                       943.00     3.00
DEPTNO ENAME                               SAL       RN
------ ------------------------------ -------- --------
    44 V_$SESSION_LONGOPS              1044.00     1.00
       V_$CLASS_PING                    994.00     2.00
       V_$DLM_LATCH                     944.00     3.00
    45 V$SESSION_LONGOPS               1045.00     1.00
       V$CLASS_PING                     995.00     2.00
       V$DLM_LATCH                      945.00     3.00
    46 V_$RESOURCE                     1046.00     1.00
       V_$INSTANCE_CACHE_TRANSFER       996.00     2.00
       V_$DLM_CONVERT_LOCAL             946.00     3.00
DEPTNO ENAME                               SAL       RN
------ ------------------------------ -------- --------
    47 V$RESOURCE                      1047.00     1.00
       V$INSTANCE_CACHE_TRANSFER        997.00     2.00
       V$DLM_CONVERT_LOCAL              947.00     3.00
    48 V_$_LOCK                        1048.00     1.00
       V_$BUFFER_POOL                   998.00     2.00
       V_$DLM_CONVERT_REMOTE            948.00     3.00
    49 V$_LOCK                         1049.00     1.00
       V$BUFFER_POOL                    999.00     2.00
DEPTNO ENAME                               SAL       RN
------ ------------------------------ -------- --------
    49 V$DLM_CONVERT_REMOTE             949.00     3.00
150 rows selected.
SQL> drop table t;
Table dropped.
SQL>