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