Oracle Illustrated

Jun 25 2010   9:10AM GMT

Analytical way – TO FIND LAST N ROWS AND DISPLAY ROWNUMBER IN A TABLE



Posted by: Lakshmi Venkatesh
Tags:

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 * 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

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: