Let's say you have a table of inventory items for an electronics store. The primary key is an integer id, and there are no other constraints on the table. Other columns are
type, brand, model, price, etc
some sample rows are
id | item | brand | model | price
---------------------------------------------------
1 tv sharp acquos 1299
2 tv sony bravia 1150
3 music apple ipod classic 249
4 music apple ipod touch 299
5 tv sharp acquos 1399
...
this is a contrived example, but imagine a complicated database table with 10000 rows. how would we quickly find items with duplicate entries?
SELECT COUNT(*) as number, brand, model FROM inventory GROUP BY brand, model HAVING number > 1;
number | brand | model
-----------------------------------
2 sharp acquos
in general, the query we need to find duplicates is:
SELECT COUNT(*) as count_alias, selected_columns FROM table_name GROUP BY selected_columns HAVING count_alias > 1;
All Articles