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!

Improve this question
2
  • 2
    what's the expected result? Why doesn't SELECT DISTINCT ProductName,Cost FROM PRODUCTS not return what you want? Commented Aug 17, 2018 at 16:32
  • 3
    Let this be a lesson to always create a PRIMARY KEY in tables. Commented Aug 17, 2018 at 16:40

4 Answers 4

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

* 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.
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.
No need for the temp table, but I agree DISTINCT is the easiest method here.
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
Improve this answer

Comments

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

Improve this answer

Comments

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);
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.