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

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_...

Minimum / Maximum from the given list

File: Execute.java -------------------------------------------------------------------------------------- package org.developersbrain.Solutions; public class Execute { public static int min(int[] arrList){ int minV1=0; int minV2=0; int aLen=arrList.length; minV1=arrList[0]; minV2=arrList[aLen-1]; for(int i=0,j=aLen-1;i<=aLen/2;i++,j--){ if(minV1 > arrList[i]){ minV1=arrList[i]; } if(minV2 > arrList[j]){ minV2=arrList[j]; } } if(minV2<=minV1){ return minV2; }else{ return minV1; } } public static int max(int[] arrList){ int minV1=0; int minV2=0; int aLen=arrList.length; minV1=arrList[0]; minV2=arrList[aLen-1]; for(int i=0,j=aLen-1;i<=aLen/2;i++,j--){ if(minV1 < arrList[i]){ minV1=arrList[i]; } if(minV2 < arrList[j]){ minV2=arrList[j]; } } if(minV2>=minV1){ return minV2; }else{ return minV1; } } ...

SQL - Binary Search Tree

Problem Statement You are given a table, BST , containing two columns: N and P, where N represents the value of a node in BST , and P is the parent of N . Write a query to find the node type of BST ordered by the value of the node. Output one of the following for each node: · Root : If node is root node. · Leaf : If node is leaf node. · Inner : If node is neither root nor leaf node. Sample Input N P 1 2 3 2 6 8 9 8 2 5 8 5 5 null Sample Output 1 Leaf 2 Inner 3 Leaf 5 Root 6 Leaf 8 Inner 9 Leaf Explanation The BST ...

SQL ,PLSQL , Unix

Search This Blog