{"id":881,"date":"2023-05-29T20:53:38","date_gmt":"2023-05-29T20:53:38","guid":{"rendered":"https:\/\/bandittracker.com\/?p=881"},"modified":"2023-05-31T19:20:33","modified_gmt":"2023-05-31T19:20:33","slug":"snowflake-distinct-on","status":"publish","type":"post","link":"https:\/\/bandittracker.com\/snowflake-distinct-on\/","title":{"rendered":"How To Use DISTINCT ON With Snowflake"},"content":{"rendered":"\n<p>PostgreSQL&#8217;s DISTINCT ON clause is a powerful tool for filtering result sets. However, the syntax is not available in Snowflake. Thankfully, there are several methods you can use to get the same results.<\/p>\n\n\n\n<p>This article walks you through three techniques that provide the equivalent of DISTINCT ON in Snowflake. &nbsp;Each method combines the ROW_NUMBER() windows function with additional syntax.<\/p>\n\n\n\n<p>The first two methods use standard SQL syntax. The third method is non-standard SQL specific to Snowflake and some other database platforms.<\/p>\n\n\n\n<ol type=\"1\">\n<li>Use ROW_NUMBER() with a CTE<\/li>\n\n\n\n<li>Use ROW_NUMBER() with a subquery<\/li>\n\n\n\n<li>Use ROW_NUMBER() with the QUALIFY clause<\/li>\n<\/ol>\n\n\n\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_64 counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\n<p class=\"ez-toc-title\">Table of Contents<\/p>\n<label for=\"ez-toc-cssicon-toggle-item-6611113b365b9\" class=\"ez-toc-cssicon-toggle-label\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/label><input type=\"checkbox\"  id=\"ez-toc-cssicon-toggle-item-6611113b365b9\" checked aria-label=\"Toggle\" \/><nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/bandittracker.com\/snowflake-distinct-on\/#What_Is_The_DISTINCT_ON_Clause\" title=\"What Is The DISTINCT ON Clause?\">What Is The DISTINCT ON Clause?<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/bandittracker.com\/snowflake-distinct-on\/#Sample_Data_For_This_Article\" title=\"Sample Data For This Article\">Sample Data For This Article<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/bandittracker.com\/snowflake-distinct-on\/#Use_ROW_NUMBER_With_A_CTE\" title=\"Use ROW_NUMBER With A CTE\">Use ROW_NUMBER With A CTE<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/bandittracker.com\/snowflake-distinct-on\/#Use_ROW_NUMBER_With_A_Subquery\" title=\"Use ROW_NUMBER With A Subquery \">Use ROW_NUMBER With A Subquery <\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/bandittracker.com\/snowflake-distinct-on\/#Use_ROW_NUMBER_With_The_QUALIFY_Clause\" title=\"Use ROW_NUMBER() With The QUALIFY Clause\">Use ROW_NUMBER() With The QUALIFY Clause<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/bandittracker.com\/snowflake-distinct-on\/#Dont_Use_The_First_Value_Function\" title=\"Don\u2019t Use The First_Value() Function\">Don\u2019t Use The First_Value() Function<\/a><\/li><\/ul><\/nav><\/div>\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"What_Is_The_DISTINCT_ON_Clause\"><\/span>What Is The DISTINCT ON Clause?<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>The DISTINCT ON clause in PostgreSQL is a useful feature that allows you to return the first row of each group of duplicates based on certain columns.<\/p>\n\n\n\n<p>Unlike the standard DISTINCT clause, it keeps the &#8220;first&#8221; row of each group of duplicates based on a specific column or set of columns. This is easier to explain with an example.<\/p>\n\n\n\n<p>Let\u2019s say you have a stationary company that has a table of its products:<\/p>\n\n\n\n<figure class=\"wp-block-table is-style-stripes\"><table><tbody><tr><td><strong>ID<\/strong><\/td><td><strong>Name<\/strong><\/td><td><strong>Category<\/strong><\/td><\/tr><tr><td>1<\/td><td>Pen<\/td><td>Office Supplies<\/td><\/tr><tr><td>2<\/td><td>Pencil<\/td><td>School Supplies<\/td><\/tr><tr><td>3<\/td><td>Pen<\/td><td>Office Supplies<\/td><\/tr><tr><td>4<\/td><td>Pencil<\/td><td>School Supplies<\/td><\/tr><tr><td>5<\/td><td>Crayon<\/td><td>School Supplies<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p>You want to eliminate the duplicates but return the first row for each unique product-category combination. This is the syntax in PostgreSQL:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT DISTINCT ON (name, category) *\nFROM product\nORDER BY name, category, id;<\/code><\/pre>\n\n\n\n<p>This is the output:<\/p>\n\n\n\n<figure class=\"wp-block-table is-style-stripes\"><table><tbody><tr><td><strong>ID<\/strong><\/td><td><strong>Name<\/strong><\/td><td><strong>Category<\/strong><\/td><\/tr><tr><td>1<\/td><td>Pen<\/td><td>Office Supplies<\/td><\/tr><tr><td>2<\/td><td>Pencil<\/td><td>School Supplies<\/td><\/tr><tr><td>5<\/td><td>Crayon<\/td><td>School Supplies<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\">Standard DISTINCT<\/h3>\n\n\n\n<p>If you need a run through of all aspects of the more standard syntax for unique values, check out our article on <a href=\"https:\/\/bandittracker.com\/snowflake-sql-distinct\/\" data-type=\"post\" data-id=\"886\">the DISTINCT keyword in Snowflake<\/a>.<\/p>\n\n\n\n<p>It covers using it on single and multiple columns, counting unique values, and what you need to know about the treatment of null values.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Sample_Data_For_This_Article\"><\/span>Sample Data For This Article<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>If you want to follow along with the examples in this article, use this script to create the sample table and data on Snowflake:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>Create Table PRODUCT (\nId VARCHAR NOT NULL PRIMARY KEY,\nNAME VARCHAR NOT NULL,\nCATEGORY VARCHAR NOT NULL\n);\n\nINSERT INTO PRODUCT VALUES (1, 'Pen', 'Office Supplies');\nINSERT INTO PRODUCT VALUES (2, 'Pencil', 'School Supplies');\nINSERT INTO PRODUCT VALUES (3, 'Pen', 'Office Supplies');\nINSERT INTO PRODUCT VALUES (4, 'Pencil', 'School Supplies');\nINSERT INTO PRODUCT VALUES (5, 'Crayon', 'School Supplies');<\/code><\/pre>\n\n\n\n<p>If you&#8217;re still learning the basics, check out our article on <a href=\"https:\/\/bandittracker.com\/snowflake-create-table\/\" data-type=\"post\" data-id=\"870\">creating tables in Snowflake<\/a>.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Use_ROW_NUMBER_With_A_CTE\"><\/span>Use ROW_NUMBER With A CTE<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>You can achieve similar results to the PostgreSQL DISTINCT ON functionality by using the ROW_NUMBER() window function with a CTE expression.<\/p>\n\n\n\n<p>Here is the SQL for our sample data:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>WITH numbered_products AS (\n    SELECT *,\n           ROW_NUMBER() OVER (PARTITION BY name, category ORDER BY id) AS row_num\n    FROM product\n)\nSELECT id, name, category\nFROM numbered_products\nWHERE row_num = 1\nORDER BY id;<\/code><\/pre>\n\n\n\n<p>In this query, the &#8220;ROW_NUMBER() OVER (PARTITION BY name, category ORDER BY id)&#8221; expression gives each row a unique number within each partition of name and department ordered by id,<\/p>\n\n\n\n<p>The WHERE row_num = 1 clause in the outer query then only selects the first row from each of these groups, effectively mimicking the behavior of DISTINCT ON.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Use_ROW_NUMBER_With_A_Subquery\"><\/span>Use ROW_NUMBER With A Subquery <span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>You can also achieve similar results by using the ROW_NUMBER() window function with a subquery.<\/p>\n\n\n\n<p>Here is the SQL for our sample data:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT id, name, category\nFROM (\n    SELECT *,\n           ROW_NUMBER() OVER (PARTITION BY name, category ORDER BY id) AS row_num\n    FROM product\n) AS subquery\nWHERE row_num = 1\nORDER BY id;<\/code><\/pre>\n\n\n\n<p>The subquery is defined in the FROM clause and assigned the alias subquery. The main query then selects from this subquery and applies the WHERE and ORDER BY clauses just like before.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Use_ROW_NUMBER_With_The_QUALIFY_Clause\"><\/span>Use ROW_NUMBER() With The QUALIFY Clause<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>You can use the QUALIFY clause to filter the results of a window function, which can be used to achieve the same effect as DISTINCT ON.<\/p>\n\n\n\n<p>The QUALIFY clause works similarly to a WHERE clause, but it operates on the results of window functions. It&#8217;s important to note that the QUALIFY clause is specific to Snowflake and a few other SQL dialects. It\u2019s not a part of standard SQL.<\/p>\n\n\n\n<p>Here is the SQL for our sample data:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT id, name, category\nFROM product\nQUALIFY ROW_NUMBER() OVER (PARTITION BY name, category ORDER BY id) = 1\nORDER BY id;<\/code><\/pre>\n\n\n\n<p>In this query:<\/p>\n\n\n\n<ul>\n<li>the ROW_NUMBER() function assigns a unique row number within each partition<\/li>\n\n\n\n<li>the QUALIFY clause filters out all rows except the first one in each partition.<\/li>\n<\/ul>\n\n\n\n<p>This gives you the same result as ROW_NUMBER() with the CTE or subquery techniques, but in a more concise form.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Dont_Use_The_First_Value_Function\"><\/span>Don\u2019t Use The First_Value() Function<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>You may be tempted to use the First_Value() function with partitioned data. The problem is that it doesn\u2019t filter out the duplicate rows.<\/p>\n\n\n\n<p>You&#8217;ll end up with the first value for each partition in every row of that partition. It won&#8217;t eliminate rows from the result set unlike the other methods we\u2019ve looked at in this article.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>PostgreSQL&#8217;s DISTINCT ON clause is a powerful tool for filtering result sets. However, the syntax is not available in Snowflake. Thankfully, there are several methods you can use to get the same results. This article walks you through three techniques that provide the equivalent of DISTINCT ON in Snowflake. &nbsp;Each method combines the ROW_NUMBER() windows &#8230; <a title=\"How To Use DISTINCT ON With Snowflake\" class=\"read-more\" href=\"https:\/\/bandittracker.com\/snowflake-distinct-on\/\" aria-label=\"More on How To Use DISTINCT ON With Snowflake\">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\/881"}],"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=881"}],"version-history":[{"count":5,"href":"https:\/\/bandittracker.com\/wp-json\/wp\/v2\/posts\/881\/revisions"}],"predecessor-version":[{"id":891,"href":"https:\/\/bandittracker.com\/wp-json\/wp\/v2\/posts\/881\/revisions\/891"}],"wp:attachment":[{"href":"https:\/\/bandittracker.com\/wp-json\/wp\/v2\/media?parent=881"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bandittracker.com\/wp-json\/wp\/v2\/categories?post=881"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bandittracker.com\/wp-json\/wp\/v2\/tags?post=881"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}