Skip to main content

How to get recently inserted records from a table?

There are a number of ways to do that..

1.When the table is not huge
Select *
from employee
order by rowid desc;

2. Simple SQL query (To get the recent 10 records inserted in Employee table)
Select *
from (Select e.*,
rank() over (order by rowid asc) rowid_rank
from employee e)
where rowid_rank > (Select count(1)-10 from employee);

3. PLSQL block of code

/***
Apply your programming logic with the oracle-stored-unique-code..
See below piece of code.
The below code will fetch you the recent 10 records inserted in EMPLOYEE table
****

clear screen
set serveroutput on
DECLARE
rid varchar2(30);
rec employee%rowtype;
v NUMBER;
i NUMBER;
BEGIN
v:=10;
SELECT MAX(rowid) INTO rid FROM employee;
FOR i IN 1..v
LOOP
SELECT * INTO rec FROM employee e WHERE e.rowid=rid;
dbms_output.put_line(rec.emp_id||','||rec.emp_name||','||rec.dept_no);
SELECT MAX(rowid) INTO rid FROM employee WHERE rowid END LOOP;
Exception
When others then
dbms_output.put_line('Ora-Code'||SQLCODE||' Error:'||SQLERRM);

END;
/

E1005,Susilkumar,45
E1004,Susil Kumar1004,4.32
E1003,Susil Kumar1003,4.32
E1001,Susil Kumar1001,44.04
E1000,Susil Kumar1000,44
E999,Susil Kumar999,43.96
E998,Susil Kumar998,43.92
E997,Susil Kumar997,43.88
E996,Susil Kumar996,43.84
E995,Susil Kumar995,43.8

Comments

Post a Comment

Popular posts from this blog

Design and Operations ( Employee- Model )

-- DB design drop table emp_Salary; drop table emp_Designation; drop table emp; drop table Designations; drop table Departments; drop table Salary_Bands; drop table Addresses; Create table emp( emp_id number, emp_Name varchar2(20), emp_address_id varchar2(10), emp_created_date date, emp_created_by varchar2(20), constraint e_pk primary key(emp_id) ); Create table Departments( dept_id number, dept_name Varchar2(20), constraints dept_pk primary key(dept_id) ); Create table Designations( dept_id number references Departments(dept_id), desg_id number, desg_description varchar2(20), constraints desg_pk primary key(dept_id,desg_id) ); Create table emp_Designation( emp_id number references emp(emp_id), emp_des_dept_id number references departments(dept_...
Post a Comment
Read more

How to remove duplicate rows without using DISTINCT or ROWID or GROUP BY methods in Oracle?

Make use of SET operators and CTAS (Create Table AS) method.. Example: Create table tmp_tb1 as Select * from table1 intersect Select * from table1; truncate table table1; insert into table1 select * from tmp_tb1; -- This will now have removed all duplicates in the table... commit; *** You can use UNION operator in place of INTERSECT
2 comments
Read more

Identify current and prev details of a customer

  create table customer_rating( id char, name char, irrating char, jcrrating char, procDate date, flag char ); insert into customer_rating values('A','D','X','Y','28-Jul-21','M'); insert into customer_rating values('A','D','M','L','27-Jul-21','M'); Select cr.id, cr.name, cr_prev.irrating pcob_rating, cr.irrating cob_rating, cr_prev.jcrrating pcob_jcr_rating, cr.jcrrating cob_jcr_rating From Customer_Rating cr join Customer_Rating cr_Prev  ON cr.procdate = '28-Jul-21' AND cr_prev.id = cr.id AND cr_prev.procdate = (  Select max(procdate) from Customer_Rating  Where id=cr.id and procdate < cr.procdate ) ;
Post a Comment
Read more