TOP N rows from a table

Non Analytical way

SELECT * FROM scott.emp inside WHERE 5 >= (SELECT COUNT (DISTINCT outside.sal) FROM scott.emp outside
WHERE outside.sal >= inside.sal) ORDER BY inside.sal DESC;


EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- -------
7839 KING PRESIDENT 17-NOV-81 5000 10
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7566 JONES MANAGER 7839 02-APR-81 2975 20
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10

6 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1721702769

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 25 (4)| 00:00:01 |
| 1 | SORT ORDER BY | | 1 | 37 | 25 (4)| 00:00:01 |
|* 2 | FILTER | | | | | |
| 3 | TABLE ACCESS FULL | EMP | 14 | 518 | 3 (0)| 00:00:01 |
| 4 | SORT GROUP BY | | 1 | 4 | | |
|* 5 | TABLE ACCESS FULL| EMP | 1 | 4 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter( (SELECT COUNT(DISTINCT "OUTSIDE"."SAL") FROM
"SCOTT"."EMP" "OUTSIDE" WHERE "OUTSIDE"."SAL">=:B1)<=5)
5 - filter("OUTSIDE"."SAL">=:B1)


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
91 consistent gets
0 physical reads
0 redo size
1028 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
13 sorts (memory)
0 sorts (disk)
6 rows processed

SQL>

Analytical way
SQL> SELECT a.* FROM
2 (select e.*,row_number() OVER (order by rownum) rnm FROM scott.emp e) a
3 WHERE rnm BETWEEN 1 AND 6;

EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO RNM
---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800
20 1
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30 2
7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30 3
7566 JONES MANAGER 7839 02-APR-81 2975
20 4
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30 5
7698 BLAKE MANAGER 7839 01-MAY-81 2850
30 6


6 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1602237660

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1400 | 4 (25)| 00:00:01|
|* 1 | VIEW | | 14 | 1400 | 4 (25)| 00:00:01|
|* 2 | WINDOW SORT PUSHED RANK| | 14 | 518 | 4 (25)| 00:00:01|
| 3 | COUNT | | | | ||
| 4 | TABLE ACCESS FULL | EMP | 14 | 518 | 3 (0)| 00:00:01|
--------------------------------------------------------------------------------

-


Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("RNM">=1 AND "RNM"<=6)
2 - filter(ROW_NUMBER() OVER ( ORDER BY ROWNUM)<=6)


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
1140 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
6 rows processed

SQL>