Solved

Remove Duplicates based on Date

Posted on 2012-03-22
5
334 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 55

Expert Comment

by:Huseyin KAHRAMAN
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 55

Expert Comment

by:Huseyin KAHRAMAN
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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
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 shrink a transaction log file down to a reasonable size.

724 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