How to select last insert value for specific column

Question

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

now i have to select last inserting values such as

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

Possible duplicate of [How to get the last row of an Oracle a table](https://stackoverflow.com/questions/12378424/how-to-get-the-last-row-of-an-oracle-a-table) — Gholamali Irani, Dec 10 '17 at 18:56

score 3 · Answer 1 · answered Dec 10 '17 at 19:59

3

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.

answered Dec 10 '17 at 19:59

Thorsten Kettner

89,309
7
49
73

if i have add new column and insert a sysdate when inserting in second table(zxy) then i can select ? – khan Dec 11 '17 at 12:35
If the records have a date there are several ways to select the last price. One is Oracle's `KEEP LAST`, another is standard SQL's `ROW_NUMBER`. Shall I update my answer and show you how or do you want to look this up yourself? – Thorsten Kettner Dec 11 '17 at 12:54

score 0 · Answer 2 · answered Dec 10 '17 at 19:06

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;

How to select last insert value for specific column

2 Answers2