DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: delete

  1. #1
    Join Date
    Feb 2005
    Posts
    28

    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

  2. #2
    Join Date
    May 2005
    Location
    Toronto Canada
    Posts
    57
    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

  3. #3
    Join Date
    Feb 2005
    Posts
    28
    thanks

    it deleted some rows but i see some still.
    will this query works when u have a productid more than 2 rows?

  4. #4
    Join Date
    May 2005
    Posts
    31

    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.

  5. #5
    Join Date
    Jul 2002
    Location
    Northampton, England
    Posts
    612
    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...

  6. #6
    Join Date
    May 2005
    Location
    Toronto Canada
    Posts
    57
    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

  7. #7
    Join Date
    Feb 2005
    Posts
    28
    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
  •  


Click Here to Expand Forum to Full Width