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
Solved

Remove all duplicates except the first in a SQL data set

Posted on 2014-08-10
8
203 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 10

Assisted Solution

by:HuaMinChen
HuaMinChen earned 500 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 48

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 48

Expert Comment

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

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 48

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

860 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