?
Solved

Remove Duplicates based on Date

Posted on 2012-03-22
5
Medium Priority
?
341 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 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 58

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 58

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

Quick Start: DOCKER

Sometimes you just need a Quick Start on a topic in order to begin using it.. this is just what you need to know to get up and running with Docker!

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

765 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