Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Remove all duplicates except the first in a SQL data set

Posted on 2014-08-10
8
Medium Priority
?
209 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
[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
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 49

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 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

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.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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

718 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