-
delete
How do i delete the row which has the old date for the same productid in case we have multiple rows
pls help
productid -- orderid -svt -hbgm
787 -- 12-may-2005 -2345 -4444
787 -- 13-may-2005 -789 -- 878
786 -- 12-juane2004 -222 -v569
799 -- 14-may 2003 -768 -p234
the query shoudl delete 787 -- 12-may-2005 -2345 -4444
pls help
thanks
-
Something like:
Code:
SQL> SELECT * FROM t;
PRODUCT_ID ORDER_DT
---------- -----------
787 12-MAY-2005
787 13-MAY-2005
786 12-JUN-2004
799 14-MAY-2003
SQL> DELETE FROM t
2 WHERE (product_id, order_dt) NOT IN (SELECT product_id, MAX(order_dt)
3 FROM t
4 GROUP BY product_id);
1 row deleted.
SQL> SELECT * FROM t;
PRODUCT_ID ORDER_DT
---------- -----------
787 13-MAY-2005
786 12-JUN-2004
799 14-MAY-2003
TTFN
John
-
thanks
it deleted some rows but i see some still.
will this query works when u have a productid more than 2 rows?
-
Analytics rock.. analytics roll....
it deleted some rows but i see some still.
will this query works when u have a productid more than 2 rows?
How about deleting like this
delete from test where (productid ,orderdt) in (
select productid ,orderdt from (
select count(*) over (partition by productid) cnt,productid,orderdt,max(orderdt) over (partition by productid) mxdt from test)
where cnt>1 and orderdt != mxdt)
/
Note the analytical function works only from Oracle version 8i above.
Experience is a hard teacher because she gives the test first, the lesson afterwards.
-
Originally posted by bonker
Note the analytical function works only from Oracle version 8i above
And only in Enterprise Edition for 8i.
Assistance is Futile...
-
Raj:
What do you mean by "it deleted some rows but i see some still"? The only case where you should see remaining duplicate rows from my statement is if there are two records for the same product with the MAX order date. Yes, it will work when a product has more than two records.
Code:
SQL> SELECT * FROM t
2 ORDER BY product_id, order_dt DESC;
PRODUCT_ID ORDER_DT
---------- -----------
786 12-JUN-2004
787 13-MAY-2005
787 13-MAY-2005
787 12-MAY-2005
787 12-MAY-2005
787 11-MAY-2005
799 14-MAY-2003
SQL> DELETE FROM t
2 WHERE (product_id, order_dt) NOT IN (SELECT product_id, MAX(order_dt)
3 FROM t
4 GROUP BY product_id);
3 rows deleted.
SQL> SELECT * FROM t
2 ORDER BY product_id, order_dt DESC;
PRODUCT_ID ORDER_DT
---------- -----------
786 12-JUN-2004
787 13-MAY-2005
787 13-MAY-2005
799 14-MAY-2003
Bonker's solution displays the same behaviour, that is it leaves two records for product 787 on May 13. You would have to benchmark both on your tables to decide which is more performant.
John
-
thanks ..i wonder why it's not deleting here , may be something else
thanks to both of u
r
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|