Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2009
    Posts
    1

    How to delete dublicate row based on updated date

    LOC_CD DIV_CD ITM_CD ,ITM_PRICE UPD_DT
    UDH AES 124003238 50 08/05/2008
    UDH AES 124003238 387 01/04/2009
    UDH AES 124003238 136 20090327
    UDH AES 124003238 139 20090526
    UDH AES 124003238 139 20090526
    UDH AES 124003238 156 20090827
    UDH AES 124003238 152 12/06/2009
    UDH AES 124003238 159 20100220
    UDH AES 124003238 159 06/01/2010
    UDH AES 124003238 159 08/04/2010
    UDH AES 124003238 164 20100928
    UDH AES 124003238 170 20101113
    UDH AES 124003238 179 20110127
    UDH AES 124003238 181 20110317
    UDH AES 124003238 185 04/09/2011
    UDH AES 124003238 184 20110525
    UDH AES 124003238 182 07/01/2011
    UDH AES 124003238 163 20110831
    UDH AES 124003238 179 10/12/2011
    UDH AES 124003238 177 12/12/2011

    please guide me how to get latest itm price based upon updated date

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,353
    Probably not possible with your seemingly free format date.
    How will you know if 01/04/2009 is 01 Apr 2009 or Jan 04 2009

    You can try something like this
    SELECT * INTO new FROM old WHERE 1=2
    CREATE UNIQUE INDEX ix1 ON new (ITM_CD ,ITM_PRICE UPD_DT) WITH ignore_dup_key
    INSERT INTO #t2 SELECT * FROM #t1
    drop index new.ix1
    CREATE UNIQUE INDEX ix1 ON new (ITM_CD ,ITM_PRICE UPD_DT)
    sp_rename old,old_dups
    sp_rename new,old

  3. #3
    Join Date
    Feb 2012
    Location
    India,Mumbai
    Posts
    34
    Hi,
    try below query to get updated records with respect to latest date

    select LOC_CD, DIV_CD ,ITM_CD ,ITM_PRICE, UPD_DT
    from Table_name t
    where NOT EXISTS (select 1 FROM Table_name t1 where
    AND t.LOC_CD = t1.LOC_CD
    AND t.DIV_CD = t1.DIV_CD
    AND t.ITM_CD = t1.ITM_CD
    t1.ITM_PRICE > t.ITM_PRICE
    )

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •