Thursday, August 30, 2012

Finding Duplicate Rows For Multiple Columns

I had occasion recently to look for some SQL to find rows in a table where at least two columns must be unique: as an example, a table where customer_id and product_id must be unique.  You can have multiple customers with the same product_id but only one row for a particular customer can have that product_id.  (The database should have made the combination of  customer_id and product_id unique, but it wasn't done, so there were a few duplicate rows.)

SELECT  customer_id,  product_id, COUNT(*) 
FROM cust_table GROUP BY  customer_id,  product_id  HAVING (COUNT(*) > 1) 

No comments:

Post a Comment