TO FIND LAST N ROWS AND DISPLAY ROWNUMBER IN A TABLE

To find the last N and display the row number.

With the usual way to pick last set of records from any table minimum 3 joins are required. Using analytical functions the same can be reduced to single table hit to retrieve the last N rows and display the row numbers for the same. This is particularly useful when something like this needs to be achieved in a big query that has multiple joins and multiple column fetch.

NON ANALYTICAL WAY

select * from scott.emp minus select * from scott.emp where rownum <
(select count(*) – 10 from scott.emp);


SQL> select * from scott.emp minus select * from scott.emp where rownum <
2 (select count(*) - 10 from scott.emp);

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- -------
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10

11 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2564595189

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1036 | 9 (67)| 00:00:01 |
| 1 | MINUS | | | | | |
| 2 | SORT UNIQUE | | 14 | 518 | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL | EMP | 14 | 518 | 3 (0)| 00:00:01 |
| 4 | SORT UNIQUE | | 14 | 518 | 5 (20)| 00:00:01 |
|* 5 | COUNT STOPKEY | | | | | |
| 6 | TABLE ACCESS FULL| EMP | 14 | 518 | 3 (0)| 00:00:01 |
| 7 | SORT AGGREGATE | | 1 | | | |
| 8 | INDEX FULL SCAN | PK_EMP | 14 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

5 - filter( (SELECT COUNT(*)-10 FROM "SCOTT"."EMP" "EMP")>ROWNUM)


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

SQL>

ANALYTICAL WAY

SELECT qry.empno, qry.ename, qry.job, qry.mgr, qry.hiredate,qry.sal,qry.comm,qry.deptno,qry.row_desc
from
(SELECT A.*, COUNT(1) OVER (ORDER BY ROWNUM ASC) ROW_ASC, COUNT(1) OVER (ORDER BY ROWNUM DESC) ROW_DESC FROM SCOTT.EMP A) QRY where qry.row_Asc between 1 and 11


SQL> SELECT qry.empno, qry.ename, qry.job, qry.mgr, qry.hiredate,qry.sal,qry.com
m,qry.deptno,qry.row_desc
2 from (SELECT A.*, COUNT(1) OVER (ORDER BY ROWNUM ASC) ROW_ASC, COUNT(1) OVE
R (ORDER BY ROWNUM DESC) ROW_DESC FROM SCOTT.EMP A) QRY where qry.row_Asc betwee
n 1 and 11;

EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO ROW_DESC
---------- ----------
7876 ADAMS CLERK 7788 23-MAY-87 1100
20 4
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0
30 5
7839 KING PRESIDENT 17-NOV-81 5000
10 6
7788 SCOTT ANALYST 7566 19-APR-87 3000
20 7
7782 CLARK MANAGER 7839 09-JUN-81 2450
10 8
7698 BLAKE MANAGER 7839 01-MAY-81 2850
30 9
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30 10
7566 JONES MANAGER 7839 02-APR-81 2975
20 11
7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30 12
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30 13
7369 SMITH CLERK 7902 17-DEC-80 800
20 14

11 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2175649969

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

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

1 - filter("QRY"."ROW_ASC">=1 AND "QRY"."ROW_ASC"<=11)


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