row_number over partition by and order by
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>
Related examples in the same category