<\/span><\/h2>\n\n\n\nNow that you understand the basic functions, let\u2019s look at one of the most common use cases for DISTINCT: removing duplicate records from tables.<\/p>\n\n\n\n
Check if there are duplicate records<\/h3>\n\n\n\n
The first step is to identify whether there are duplicate values in your data. To do so, you can count the distinct rows or values and compare this to the total number of rows.<\/p>\n\n\n\n
If the distinct values are fewer than the total number, then you know that you have duplicates.<\/p>\n\n\n\n
Here are the two queries with our sample data:<\/p>\n\n\n\n
SELECT COUNT(DISTINCT ID, NAME, CATEGORY) FROM PRODUCT;\n\nSELECT COUNT(*) FROM PRODUCT;<\/code><\/pre>\n\n\n\nRemove Duplicate Rows<\/h3>\n\n\n\n
When you want to permanently delete duplicate rows from a table, you can do this in two steps:<\/p>\n\n\n\n
\n- Create a new table with the distinct rows.<\/li>\n\n\n\n
- Replace the original table with the reduced version.<\/li>\n<\/ol>\n\n\n\n
I\u2019ve already shown you how to run a SELECT DISTINCT across all columns in the table. Here is the full syntax to eliminate the duplicates:<\/p>\n\n\n\n
CREATE TABLE new_table AS SELECT DISTINCT * FROM original_table;\n\nDROP TABLE original_table;\n\nALTER TABLE new_table RENAME TO original_table;<\/code><\/pre>\n\n\n\nBear in mind that you will lose indexes and constraints from the original table. You will need to recreate them if you want them preserved.<\/p>\n\n\n\n
<\/p>\n\n\n\n
Bear in mind that you will lose indexes and constraints from the original table. You will need to recreate them if you want them preserved.<\/p>\n","protected":false},"excerpt":{"rendered":"
The DISTINCT keyword in SQL lets you eliminate redundancy in your data queries and results. It’s particularly important in Snowflake where data can be enormous in size and highly redundant. Using DISTINCT in Snowflake can also optimize performance when dealing with large volumes of data in the cloud. It can help in: This article runs … Read more<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[25],"tags":[],"_links":{"self":[{"href":"https:\/\/bandittracker.com\/wp-json\/wp\/v2\/posts\/886"}],"collection":[{"href":"https:\/\/bandittracker.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/bandittracker.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/bandittracker.com\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/bandittracker.com\/wp-json\/wp\/v2\/comments?post=886"}],"version-history":[{"count":2,"href":"https:\/\/bandittracker.com\/wp-json\/wp\/v2\/posts\/886\/revisions"}],"predecessor-version":[{"id":889,"href":"https:\/\/bandittracker.com\/wp-json\/wp\/v2\/posts\/886\/revisions\/889"}],"wp:attachment":[{"href":"https:\/\/bandittracker.com\/wp-json\/wp\/v2\/media?parent=886"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bandittracker.com\/wp-json\/wp\/v2\/categories?post=886"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bandittracker.com\/wp-json\/wp\/v2\/tags?post=886"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}