Inserting Latest Record.

smartcooldevil · #1 (**permalink**) 07-12-11, 12:56

Below is my table struture call TABLE_A

PRODUCT_ID INVOICE_NBR CREATION_DT CHARGE_DT INVOICE_AMT IFEE INVOICE_STATUS
805526906 F1358412 5/29/2001 5/1/2001 0 INITIAL PSACCPT
805526906 F1670427 7/30/2008 7/1/2008 5000 INITIAL PSACCPT
808639021 F1520056 7/25/2005 7/1/2005 0 INITIAL PSACCPT
808639021 F1663752 6/5/2008 6/1/2008 5000 INITIAL PSACCPT

Now I have created one temporary table call TEMP in which I need to INSERT data from ablove table(TABLE_A).

I need to insert all the fields from above table but the it should be latest invoice charged(CHARGE_DT) on Particular _Product.

Please tell me the syntax or inbuilt function that I can use to grab the latest record in my INSERT statement.
This is just a sample data, but I have many columns from different tables to be reported so GROUP BY cluase will be not a feasible approach.
Inshort I need below two rows to be inserted in my TEMP table.

PRODUCT_ID INVOICE_NBR CREATION_DT CHARGE_DT INVOICE_AMT INITIAL INVOICE_STATUS
805526906 F1670427 7/30/2008 7/1/2008 5000 INITIAL PSACCPT
808639021 F1663752 6/5/2008 6/1/2008 5000 INITIAL PSACCPT

ARWinner · #2 (**permalink**) 07-12-11, 13:04

Code:

declare global temporary table x (product_id int, charge_dt date) on commit preserve rows not logged with replace;

insert into session.x 
select product_id,max(charge_dt) 
from table_a
group by product_id;

insert into temp 
select * from table a
where (product_id,charge_dt) in (select product_id,charge_dt from session.x);

Andy

smartcooldevil · #3 (**permalink**) 07-12-11, 13:48

Thanks Andy,

One more question.
Can this be done without any extra temporaray table?

ARWinner · #4 (**permalink**) 07-12-11, 14:07

You can try this:

Code:

insert into TEMP
with t1 (product_id,charge_dt) as
(select product_id,max(charge_dt) from table_a group by product_id)
select * from table_a 
where (product_id,charge_dt) in (select product_id,charge_dt from t1)

Andy

tonkuma · #5 (**permalink**) 07-12-11, 14:37

Example (a):

Code:

INSERT INTO temp
SELECT product_id , invoice_nbr , creation_dt , charge_dt
     , invoice_amt , ifee , invoice_status
 FROM  (SELECT a.*
             , ROW_NUMBER()
                  OVER(PARTITION BY product_id
                           ORDER BY charge_dt DESC) AS r_n
         FROM  table_a a
       ) a
 WHERE r_n = 1
;

See this thread too.
Max and group by gives error

In Example (a),
PARTITION BY was addded to select within each product_id
and ROW_NUMBER was used instead of RANK to gurantee only one row to be selected for each product_id.

Use ROW_NUMBER or RANK depending on your requirements.

Internet Services

Web Development

Digital Marketing

Consumer Tech