Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 211
  • Last Modified:

Remove all duplicates except the first in a SQL data set

Hello

I have a SQL Script that returns a set of Data, I need to remove the duplicates in a Certain column.

When there is a Duplicate value in the MinQty Column I need to remove all the Duplicate values and only Keep the FIRST of the duplicate records.

Following is a Screenshot of some Dummy Data.
Sample Data
Here is what Results I Need.
Data Result Sample
Many thanks
0
p-plater
Asked:
p-plater
2 Solutions
 
HuaMinChenBusiness AnalystCommented:
Hi,
Assume tab1 is the table name, try

with cte as
(select code,amount,minqty,ROW_NUMBER() OVER(ORDER BY code) rn from tab1)
select code,amount,minqty
from cte
where rn=1

Open in new window

0
 
PortletPaulCommented:
test by selecting first, adjust number of of deletions if large.

SELECT
      minQty
    , MIN(Code)
FROM Sample
GROUP BY
      minQty
HAVING COUNT(*) > 1
;


DELETE TOP (10000) FROM S --<< adjust to suit
      FROM Sample S
      INNER JOIN (
            SELECT
                  minQty
                , MIN(Code) AS min_code
            FROM Sample
            GROUP BY
                  minQty
            HAVING COUNT(*) > 1
      ) AS dups
            ON s.minQty = dups.minQty
            AND s.code > dups.min_code -->> IMPORTANT! use just greater than
;

see: http://sqlfiddle.com/#!3/33ac9/3

Open in new window

0
 
p-platerAuthor Commented:
Edited HuaMinChen's Solution to Work Correctly

with cte as
(select code,amount,minqty,ROW_NUMBER() OVER(PARTITION BY MINQTY ORDER BY AMOUNT) rn from tab1)
select code,amount,minqty
from cte
where rn=1
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
PortletPaulCommented:
apparently you didn't try mine (which was complete)
0
 
Scott PletcherSenior DBACommented:
I don't object to the closing request.  I just want to make sure the solution is correct.

You said you wanted the "first" of duplicates kept.
Do you want the minimum "Amount" value when there are duplicate "MinQty" values?
Or do you want the lowest-numbered "Code" value?

As written, the code gives you the smallest Amount value, but you may want the lowest Code value instead.
0
 
p-platerAuthor Commented:
I see what happened now - When I make the sample data in Excel I dragged the code and it incremented it by 1 each row. :(
(The code was supposed to be the same for each record)

I Need the Smallest Amount PER Code and Min Qty.

Sorry for the Confusion

Thanks Everyone
0
 
p-platerAuthor Commented:
Had to Edit to work correctly
0
 
PortletPaulCommented:
Some observations:

If the edits arise from information not made available to the expert, should the expert be graded down?
or denied Accepted Answer?

Having a need to edit the proposed solution isn't reason enough to downgrade the result particularly as it is the technique employing row_number() and "rn=1" that is the essence of that answer.

Is that edit "substantial"?
with cte as
(select code,amount,minqty,ROW_NUMBER() OVER(ORDER BY code) rn from tab1)
select code,amount,minqty
from cte
where rn=1

with cte as
(select code,amount,minqty,ROW_NUMBER() OVER(PARTITION BY MINQTY ORDER BY AMOUNT) rn from tab1)
select code,amount,minqty
from cte
where rn=1

Open in new window

At least part of that change is due to the mistake in the supplied data (PARTITION BY MINQTY) isn't it?
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now