Retrieve the Nth row from a table

To retrieve Nth row from a table using the usual way it might require to query the same table thrice (can write it in different way also). But, with Analytical function the same can be made to hit only once. Using this kind of analytical functions is particularly useful when the query needs to be part of a huge query - helps to avoid group by - so the need to query the same table multiple times is reduced.

Non Analytical way

One way to do this - Usual - three table hits.

select sal FROM scott.emp WHERE rowid = (SELECT rowid FROM scott.emp WHERE rownum <= 5 MINUS SELECT rowid FROM scott.emp WHERE rownum < 5);

SAL
----------
1250

Execution Plan
----------------------------------------------------------
Plan hash value: 791824717

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 16 | 5 (40)| 00:00:01 |
| 1 | TABLE ACCESS BY USER ROWID| EMP | 1 | 16 | 1 (0)| 00:00:01 |
| 2 | MINUS | | | | | |
| 3 | SORT UNIQUE | | 5 | 60 | 2 (50)| 00:00:01 |
|* 4 | COUNT STOPKEY | | | | | |
| 5 | INDEX FULL SCAN | PK_EMP | 14 | 168 | 1 (0)| 00:00:01 |
| 6 | SORT UNIQUE | | 4 | 48 | 2 (50)| 00:00:01 |
|* 7 | COUNT STOPKEY | | | | | |
| 8 | INDEX FULL SCAN | PK_EMP | 14 | 168 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------

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

4 - filter(ROWNUM<=5)
7 - filter(ROWNUM<5)


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


Using Analytical functions


SQL> SELECT a.* FROM
(select e.*,row_number() OVER (order by rownum) rnm FROM scott.emp e) a
WHERE rnm = 5;

EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO RNM
---------- ----------
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30 5

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"=5)
2 - filter(ROW_NUMBER() OVER ( ORDER BY ROWNUM)<=5)


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
892 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)
1 rows processed