DISTINCT

Description

DISTINCT eliminates duplicate rows from the result. SELECT DISTINCT ON eliminates rows that match on all the specified expressions.

Click here to navigate to the PostgreSQL documentation page for this syntax.

DISTINCT case

SELECT DISTINCT is supported by Snowflake.

Grammar Syntax

SELECT
   DISTINCT column1 [, ...]
FROM
   table_name;

Sample Source Patterns

Input

PostgreSQL

SELECT DISTINCT id, name FROM sample;

Snowflake

SELECT DISTINCT id, name FROM sample;

Output

PostgreSQL

id|name  |
--+------+
 1|Gokhan|
 2|David |
 2|Jawwad|
 4|Josue |
 1|Jack  |
 3|Josue |

Snowflake

id|name  |
--+------+
 1|Gokhan|
 1|Jack  |
 2|Jawwad|
 2|David |
 4|Josue |
 3|Josue |

DISTINCT ON case

The SELECT DISTINCT ON is translated to SELECT WITH QUALIFY to mimic the functionality.

Grammar Syntax

SELECT
   DISTINCT ON (column1,  [...,]) column2, [...,]
FROM
   table_name;

Sample Source Patterns

Input

PostgreSQL

SELECT DISTINCT ON (id) id, name FROM sample;

Snowflake

SELECT id, name FROM sample
QUALIFY ROW_NUMBER() OVER (PARTITION BY (ID) ORDER BY (id)) = 1;

This Snowflake code selects unique rows from a table called sample in Snowflake based only on the values in the "id" column. It does this by using the ROW_NUMBER() function to assign a unique row number to each row in the table, then filtering out rows that have a row number greater than 1 using the QUALIFY clause. This results in only one unique row being selected for each unique "id" value.

Output

PostgreSQL

id|name  |
--+------+
 1|Gokhan|
 2|Jawwad|
 3|Josue |
 4|Josue |

Snowflake

id|name  |
--+------+
 1|Gokhan|
 2|Jawwad|
 3|Josue |
 4|Josue |