Quickly Check for Duplicates using SQL
If you are writing queries, you often need to check the table or result for duplicates. This will be a short, straightforward demonstration of how to check duplicates using SQL, specifically COUNT(), COUNT(DISTINCT ) and HAVING clauses:
Example:
checking if order_ids are unique in a particular table or if there is duplicate
SELECT COUNT(order_id) as order_count , COUNT(DISTINCT order_id) as order_distinct_count FROM database.schema.table
If order_count and order_distinct_count are different, there are duplicates. If so, you can check examples by querying the following:
SELECT Order_id, COUNT(order_id) as counts FROM database.schema.table GROUP BY Order_id HAVING counts > 1
Then you can examine further for a few of those order_id if needed:
SELECT * FROM database.schema.table WHERE order_id = ‘example order id you got from the previous query’
Kommentare