How do you retrieve the last N records from a table?

Categories >> Software >> Databases >> SQL PLSQL
Suggest New Category

Question Posted / meerjasa.s

10 Answers
16756 Views
I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

How do you retrieve the last N records from a table?..

Answer / nani

select * from emp a where &N > (select count(*) from emp b
where b.rowid > a.rowid)

N = No.of rows.

Is This Answer Correct ?

8 Yes

0 No

How do you retrieve the last N records from a table?..

Answer / swastik

select
* from
(
select e1.*
from emp e1
order by rownum desc
)
where rownum <= &n

Is This Answer Correct ?

1 Yes

0 No

How do you retrieve the last N records from a table?..

Answer / eshwer

Nani is currect....

We can also write observe the querey

SQL> select * from emp;

EMPNO ENAME JOB ---------- ---------- 7369 SMITH CLERK 7499 ALLEN SALESMAN 7521 WARD SALESMAN 7566 JONES MANAGER 7654 MARTIN SALESMAN 7698 BLAKE MANAGER 7782 CLARK MANAGER 7788 SCOTT ANALYST 7839 KING PRESIDENT 7844 TURNER SALESMAN 7876 ADAMS CLERK 7900 JAMES CLERK 7902 FORD ANALYST 7934 MILLER CLERK
14 rows selected.

SQL> SELECT * FROM(SELECT Enter value for n: 5
old 1: SELECT * new 1: SELECT *
EMPNO ENAME JOB ---------- ---------- 7844 TURNER SALESMAN 7876 ADAMS CLERK 7900 JAMES CLERK 7902 FORD ANALYST 7934 MILLER CLERK
SQL> the following quere
MGR HIREDATE SAL COMM DEPTNO
--------- ---------- --------- ---------- ---------- ----------
7902 17-DEC-80 800 20
7698 20-FEB-81 1600 300 30
7698 22-FEB-81 1250 500 30
7839 02-APR-81 2975 20
7698 28-SEP-81 1250 1400 30
7839 01-MAY-81 2850 30
7839 09-JUN-81 2450 10
7566 19-APR-87 3000 20
17-NOV-81 5000 10
7698 08-SEP-81 1500 0 30
7788 23-MAY-87 1100 20
7698 03-DEC-81 950 30
7566 03-DEC-81 3000 20
7782 23-JAN-82 1300 10
EMP.*,ROWNUM R FROM EMP) WHERE R>(SELECT COUNT(*) -&N FROM EMP);
FROM(SELECT EMP.*,ROWNUM R FROM EMP) WHERE R>(SELECT COUNT(*) -&N FROM EMP)
FROM(SELECT EMP.*,ROWNUM R FROM EMP) WHERE R>(SELECT COUNT(*) -5 FROM EMP)
MGR HIREDATE SAL COMM DEPTNO R
--------- ---------- --------- ---------- ---------- ---------- ----------
7698 08-SEP-81 1500 0 30 10
7788 23-MAY-87 1100 20 11
7698 03-DEC-81 950 30 12
7566 03-DEC-81 3000 20 13
7782 23-JAN-82 1300 10 14

Is This Answer Correct ?

1 Yes

1 No

How do you retrieve the last N records from a table?..

Answer / ajit

SELECT RN, Ename
FROM ( SELECT Rownum RN, Ename
FROM Emp )
WHERE RN = &Grecno;

Is This Answer Correct ?

0 Yes

0 No

How do you retrieve the last N records from a table?..

Answer / rajat

SELECT * FROM EMP A
WHERE ROWID NOT IN
(SELECT ROWID FROM RAJ WHERE ROWNUM<=(SELECT COUNT(1) FROM RAJ )-10);

Is This Answer Correct ?

0 Yes

0 No

How do you retrieve the last N records from a table?..

Answer / manas ranjan

the RANK() and DENSE_RANK() functions can be used to
determine the LAST N or BOTTOM N rows.

Is This Answer Correct ?

1 Yes

2 No

How do you retrieve the last N records from a table?..

Answer / sunil bisht

Last 10 record from emp table

select * from emp where rowid<=(select max(rowid) from emp)
minus select * from emp where rowid in(select rowid from
emp where rownum<=3);

Is This Answer Correct ?

0 Yes

2 No

How do you retrieve the last N records from a table?..

Answer / a g srikanth

SELECT *
FROM emp e
WHERE 1>
(SELECT COUNT(1) FROM emp f WHERE e.rowid<f.rowid
);

Is This Answer Correct ?

0 Yes

2 No

How do you retrieve the last N records from a table?..

Answer / kiran penujuri

This Will give you last 10 records from a table

SELECT EMPNAME,SALARY
FROM
(SELECT EMPNAME,
SALARY,
RANK() OVER(ORDER BY SALARY) SAL_RANK
FROM EMP)
WHERE SAL_RANK < = 10

Is This Answer Correct ?

1 Yes

5 No

How do you retrieve the last N records from a table?..

Answer / jyoti

We can retrieve last N records using Order by clause in the
query.

The ORDER BY clause using DESC
then give limits 0,N
for ex: SELECT * from table_name OREDER BY id DESC limit 0,10

It will return you last 10 records of the table.

Is This Answer Correct ?

1 Yes

7 No

Post New Answer