CODEWITHSUNDEEP
sqloracle-databaseselect
khan
khan

Reputation: 79

How to select last insert value for specific column

I have a two tables and details are give below:

 create table pbc(
 id number(5) primary key,
 name varchar2(15));
 insert into pbc values(2,'product1');
 insert into pbc values(3,'product1');
 insert into pbc values(4,'product1');
 insert into pbc values(5,'product1');
 insert into pbc values(6,'product1');
 insert into pbc values(7,'product1');

and the other table is 

 create table zxy(
 id number(5),
 price number(10));
 alter table zxy add(constraint zxyid_fk FOREIGN KEY (id) references  pbc(id));
 insert into zxy values(2,67);
 insert into zxy values(3,34);
 insert into zxy values(3,21);
 insert into zxy values(4,65);
 insert into zxy values(5,32);
 insert into zxy values(5,23);
 insert into zxy values(5,10);

second table select data are given below 

Id   price
2    67
3    34
3    21
4    65
5    32
5    23
5    10

now i have to select last inserting values such as

id  price 
2   67
3   21
4   65
5   10

I do not want to max price I want to last insert price

Upvotes: 0

Views: 457

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 95072

There is no way to say which value was inserted last. For ID 3 there are two prices 34 and 21, but nothing to indicate when the records got inserted. Data in tables has no inherent order; the records are considered unordered.

You need a date or something to indicate insert order.

If the table already exists this way, you are lost, because you cannot know which values are current and which are out-dated. If this is a new table and you only plan to fill it later, then add a date and a trigger to fill the date with sysdate on insert.

Upvotes: 3

Vash
Vash

Reputation: 1787

Based on the requirements, I don't see any usage of the first table. Try: 

SELECT Z.ID, B.PRICE
FROM
(
    SELECT A.ID, MAX(A.RNUM) AS LAST_INSERTED
    FROM
    (SELECT ID, PRICE, ROW_NUMBER() OVER (PARTITION BY ID) AS RNUM FROM ZXY) A
    GROUP BY A.ID
) Z
INNER JOIN
(SELECT ID, PRICE, ROW_NUMBER() OVER (PARTITION BY ID) AS RNUM FROM ZXY) B
ON Z.ID = B.ID AND Z.LAST_INSERTED = B.RNUM;

Upvotes: 0

Related Questions