Solved

Remove Duplicates based on Date

Posted on 2012-03-22
5
321 Views
Last Modified: 2012-08-14
I am tiring to remove rows with duplicate serial numbers in a select statement, I do not actually want to delete them from the table as this is a history table, The Dups were caused by redoing an order that was incorrect, and the issue is that I want to remove the dup with the older date. I attempted to do this with a CTE (See Code) it works for the most part but it returns the older date as opposed to the last date. Any help would be greatly appreciated.  

 WITH CTE
AS
(
  SELECT *, ROW_NUMBER() OVER (PARTITION BY SERLTNUM ORDER BY DOCDATE DESC) as RowNumber
  FROM (SELECT     SOP30200.SOPNUMBE, SOP30200.DOCDATE, SOP10201.ITEMNMBR, SOP30300.ITEMDESC, SOP10201.SERLTNUM, SOP30200.CUSTNMBR, SOP30200.CUSTNAME, 
                      SOP30200.ShipToName, SOP30200.ADDRESS1, SOP30200.ADDRESS2, SOP30200.ADDRESS3, SOP30200.CITY, SOP30200.STATE, SOP30200.ZIPCODE, 
                      SOP30200.COUNTRY, SOP30300.QUANTITY, SOP30300.QTYFULFI, SOP30300.QTYTOINV, IV00101.ITMCLSCD
FROM         SOP30200 INNER JOIN
                      SOP10201 ON SOP30200.SOPTYPE = SOP10201.SOPTYPE AND SOP30200.SOPNUMBE = SOP10201.SOPNUMBE INNER JOIN
                      SOP30300 ON SOP10201.SOPTYPE = SOP30300.SOPTYPE AND SOP10201.SOPNUMBE = SOP30300.SOPNUMBE AND 
                      SOP10201.LNITMSEQ = SOP30300.LNITMSEQ AND SOP10201.CMPNTSEQ = SOP30300.CMPNTSEQ AND SOP10201.ITEMNMBR = SOP30300.ITEMNMBR INNER JOIN
                      RM00101 ON SOP30200.CUSTNMBR = RM00101.CUSTNMBR INNER JOIN
                      IV00101 ON SOP10201.ITEMNMBR = IV00101.ITEMNMBR
GROUP BY SOP30200.SOPTYPE, SOP30200.SOPNUMBE, SOP30200.DOCDATE, SOP10201.SERLTNUM, SOP30200.CUSTNMBR, SOP30200.CUSTNAME, 
                      SOP30200.ShipToName, SOP30200.ADDRESS1, SOP30200.ADDRESS2, SOP30200.ADDRESS3, SOP30200.CITY, SOP30200.STATE, SOP30200.ZIPCODE, 
                      SOP30200.COUNTRY, SOP10201.ITEMNMBR, SOP30300.ITEMDESC, RM00101.CUSTCLAS, SOP30300.QUANTITY, SOP30300.QTYFULFI, SOP30300.QTYTOINV, 
                      IV00101.ITMCLSCD
                      HAVING   (RM00101.CUSTCLAS = 'EXP')AND(SOP30200.SOPTYPE <> 4)) AS E)

SELECT SOPNUMBE, DOCDATE, ITEMNMBR,ITEMDESC,SERLTNUM,CUSTNMBR, CUSTNAME, 
ShipToName, ADDRESS1,ADDRESS2, ADDRESS3, CITY, STATE, ZIPCODE, 
COUNTRY, QUANTITY, QTYFULFI, QTYTOINV, ITMCLSCD
FROM CTE
WHERE RowNumber = 1           
ORDER BY SOPNUMBE,CUSTNMBR,ITEMNMBR
      

Open in new window

0
Comment
Question by:skull52
  • 2
  • 2
5 Comments
 
LVL 18

Accepted Solution

by:
deighton earned 500 total points
ID: 37753081
ss in the first line, do you need

PARTITION BY SERLTNUM ORDER BY DOCDATE ASC

in place of

PARTITION BY SERLTNUM ORDER BY DOCDATE DESC
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 37753544
I see

ROW_NUMBER() OVER (PARTITION BY SERLTNUM ORDER BY DOCDATE DESC) as RowNumber

which is correct... can you please post a sample data returned from this query by changing

WHERE RowNumber = 1
>>>
WHERE RowNumber < 4

and post a sample data for one SERLTNUM. I want to see what records you have and what order they are selected...
0
 

Author Comment

by:skull52
ID: 37754201
Deighton,
 I had the order set to DESC <ROW_NUMBER() OVER (PARTITION BY SERLTNUM ORDER BY DOCDATE DESC) > which I thought should work it didn't at first but now it orders as it should with the newest date at row position 1, Strange... I must have missed something at first

HainKurt,
Changing the RowNumber to < 4 displays all rows including the DUP
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 37754548
Changing the RowNumber to < 4 displays all rows including the DUP

I know :) it will display 3 records per SERLTNUM, is the order correct? most recent on top with RowNumber=1, ...
0
 

Author Comment

by:skull52
ID: 37759182
HainKurt,
Yep it did.
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now