How do you retrieve the last N records from a table?
Answers were Sorted based on User's Feedback
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 |
Answer / swastik
select
* from
(
select e1.*
from emp e1
order by rownum desc
)
where rownum <= &n
Is This Answer Correct ? | 1 Yes | 0 No |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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?
How do I run a sql query in pgadmin 4?
What is a schema? How is it useful in sql servers?
What is record variable?
Is trigger a stored procedure?
How to fix oracle error ora-00942: table or view does not exist
Is inner join faster than left join?
What is a loop in sql?
Is it possible to create the following trigger: before or after update trigger for each row?
how many columns can be used for creating index? : Sql dba
How show all rows in sql?
What are sql functions? Describe the different types of sql functions?