?
Solved

Remove Duplicates based on Date

Posted on 2012-03-22
5
Medium Priority
?
355 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 2000 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 61

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 61

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Suggested Courses

864 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