March 12, 2014 at 8:04 am
There is a member table in which members info was stored but may have duplicate records.
I used code below to delete duplicator but want to keep the latest [CreatedDate]. For example, one memberID is 12345 was stored more than one time but [CreateDate] are 2/1/2001, 3/15/2011, 8/20/2012. I want to keep only one record, that is [CreateDate]='8/20/2012'
WITH numbered
AS (SELECT MEMBERID,
Row_number()
OVER (
PARTITION BY MEMBERID
ORDER BY MEMBERID, [CreateDate] ) AS nr
FROM [Member])
DELETE FROM numbered
WHERE nr > 1
But the code did not work as I expected.
What is wrong?
March 12, 2014 at 8:14 am
adonetok (3/12/2014)
There is a member table in which members info was stored but may have duplicate records.I used code below to delete duplicator but want to keep the latest [CreatedDate]. For example, one memberID is 12345 was stored more than one time but [CreateDate] are 2/1/2001, 3/15/2011, 8/20/2012. I want to keep only one record, that is [CreateDate]='8/20/2012'
WITH numbered
AS (SELECT MEMBERID,
Row_number()
OVER (
PARTITION BY MEMBERID
ORDER BY MEMBERID, [CreateDate] ) AS nr
FROM [Member])
DELETE FROM numbered
WHERE nr > 1
But the code did not work as I expected.
What is wrong?
Kind of a shot in the dark because you didn't really say what it did. Stating that it didn't work as expected doesn't explain what it did and what you expect. I think that you are trying to only keep the most recent one? You would need to order by CreateDate DESC.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 12, 2014 at 8:25 am
Yes, once change to desc, it works.
Thnaks,
March 12, 2014 at 8:27 am
Cool glad that worked for you.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply