If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Sybase > How to delete dublicate row based on updated date

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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
)
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On