0

My question may sound weird, but please bear with me. This is what I have:

Table Products:

ProductName     Cost   
Pen A           1.00  
Pen A           1.00  
Pen B           1.00   
Pen A           2.00  
Pen C           3.00  
Pen C           3.00  
Pen C           3.00

How do I remove true duplicates from this? If you look at the data, you can see that Pen C has three rows with exact same data. Pen A has 2 rows with same data and 1 with a different cost. I don't want to eliminate "Pen A".

A simple group by statement like this:

SELECT ProductName,Cost FROM PRODUCTS GROUP BY ProductName,Cost HAVING COUNT(*) > 1

This wont' work since it'll pick up "PEN A" as well.

Any help is appreciated. Thank you!

UUser
  • 59
  • 8
  • 2
    what's the expected result? Why doesn't `SELECT DISTINCT ProductName,Cost FROM PRODUCTS` not return what you want? – S3S Aug 17 '18 at 16:32
  • 3
    Let this be a lesson to always create a `PRIMARY KEY` in tables. – Eric Aug 17 '18 at 16:40

4 Answers4

1

A quick way without using join or subqueries is to create a table PRODUCTS_TEMP with the same structure and insert just the unique rows:

INSERT INTO PRODUCTS_TEMP 
SELECT DISTINCT ProductName,Cost FROM PRODUCTS;

Then you can delete your PRODUCTS table and rename PRODUCTS_TEMP to PRODUCTS:

DROP TABLE PRODUCTS;
ALTER TABLE PRODUCTS_TEMP RENAME TO PRODUCTS;

Now you have your table filtered.

T30
  • 11,422
  • 7
  • 53
  • 57
  • * Note I'm using Mysql syntax since I don't have a sqlServer instance ready for testing. Feel free to update this if you find any incompatibilities. – T30 Aug 17 '18 at 16:32
  • Also need to remember to script PKs, FKs, indexes, and triggers, if applicable. If any of these are required, I'd go with John's answer. – HardCode Aug 17 '18 at 16:33
  • No need for the temp table, but I agree `DISTINCT` is the easiest method here. – S3S Aug 17 '18 at 16:38
1

You can use a CTE in concert with Row_Number() to remove Dupes

Example

;with cte as (
    Select * 
          ,RN = Row_Number() over (Partition By ProductName,Cost Order By (Select null))
    from PRODUCTS 
)
Delete from cte where RN>1

Updated Table

ProductName Cost
Pen A       1.00
Pen B       1.00
Pen A       2.00
Pen C       3.00
John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
0

After cleaning the mess in any way you like, you might like to consider to create a constraint index there with that 2 columns and/or validate to get sure that combination doesn't exists before inserting new rows.

Good luck

ca.po. Rodriguez
  • 46
  • 2
0

I think you want products that have only one cost. If so:

select productname
from products
group by productname
having min(cost) = max(cost);
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786