[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Remove all duplicates except the first in a SQL data set

Posted on 2014-08-10
8
Medium Priority
?
216 Views
Last Modified: 2014-08-15
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
Comment
Question by:p-plater
8 Comments
 
LVL 11

Assisted Solution

by:HuaMinChen
HuaMinChen earned 1500 total points
ID: 40252502
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40252527
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
 

Accepted Solution

by:
p-plater earned 0 total points
ID: 40252543
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
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
LVL 49

Expert Comment

by:PortletPaul
ID: 40252566
apparently you didn't try mine (which was complete)
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40253234
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
 

Author Comment

by:p-plater
ID: 40254623
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
 

Author Closing Comment

by:p-plater
ID: 40262637
Had to Edit to work correctly
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40262714
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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
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.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

612 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