Solved

Remove Duplicates based on Date

Posted on 2012-03-22
5
323 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.

914 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

13 Experts available now in Live!

Get 1:1 Help Now