Mega Code Archive

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>