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

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 the following quere
observe the querey

SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
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

14 rows selected.




SQL> SELECT * FROM(SELECT EMP.*,ROWNUM R FROM EMP) WHERE R>(SELECT COUNT(*) -&N FROM EMP);
Enter value for n: 5
old 1: SELECT * FROM(SELECT EMP.*,ROWNUM R FROM EMP) WHERE R>(SELECT COUNT(*) -&N FROM EMP)
new 1: SELECT * FROM(SELECT EMP.*,ROWNUM R FROM EMP) WHERE R>(SELECT COUNT(*) -5 FROM EMP)

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO R
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 10
7876 ADAMS CLERK 7788 23-MAY-87 1100 20 11
7900 JAMES CLERK 7698 03-DEC-81 950 30 12
7902 FORD ANALYST 7566 03-DEC-81 3000 20 13
7934 MILLER CLERK 7782 23-JAN-82 1300 10 14

SQL>

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

More SQL PLSQL Interview Questions

create a store procedure and created synonms for that store procedure after modify that store procedure will effect on synonms? If we delete the store procedure what happened to that synonms?

2 Answers   Polaris,


How do I run a sql query in pgadmin 4?

0 Answers  


What is a schema? How is it useful in sql servers?

0 Answers  


What is record variable?

0 Answers  


Is trigger a stored procedure?

0 Answers  






How to fix oracle error ora-00942: table or view does not exist

0 Answers  


Is inner join faster than left join?

0 Answers  


What is a loop in sql?

0 Answers  


Is it possible to create the following trigger: before or after update trigger for each row?

0 Answers  


how many columns can be used for creating index? : Sql dba

0 Answers  


How show all rows in sql?

0 Answers  


What are sql functions? Describe the different types of sql functions?

0 Answers  


Categories