Using `DISTINCT ON` in Snowflake

Question

I have a below query where I need to do a DISTINCT ON the allowed_id column from the union result, as is possible in PostgreSQL. I have read that Snowflake uses similar kind of PostgreSQL but DISTINCT ON didn't work.

select distinct on (allowed_id), *  from (
  select listagg(distinct id) as allowed_id,   count (people) as totalpeople ,max(score) as maxscore , min(score) as minscore, 'n' as type from tableA
         where userid = 123  

  union
  select listagg(distinct id) as allowed_id,  count (people) as totalpeople, max(elscore) as maxscore , min(elscore) as minscore, 'o' as type from tableB
         where userid = 123 
   union
   select listagg(distinct id) as allowed_id, null, null , null , 'j' as type from tableC
         where userid = 123 
    union 
    select listagg(distinct id) as allowed_id, null, null , null , 'a' as type from tableD
         where userid = 123 
   )

Gokhan Atil Gokhan Atil · Accepted Answer · 2020-02-13T11:56:10

Snowflake does not support "DISTINCT ON", but you can use QUALIFY and ROW_NUMBER to produce the same result:

SELECT * from (
select * from values (123,11,12,'a' ) as tableA (allowed_id, col2, col3, table_name)
union all
select * from values (123,21,22,'b' ) as tableA (allowed_id, col2, col3, table_name)
union all
select * from values (123,31,32,'c' ) as tableA (allowed_id, col2, col3, table_name)
union all
select * from values (123,41,42,'d' ) as tableA (allowed_id, col2, col3, table_name)
) 
where allowed_id = 123
QUALIFY ROW_NUMBER() OVER (PARTITION BY allowed_id ORDER BY allowed_id) = 1 ;

Please check:

https://docs.snowflake.net/manuals/sql-reference/constructs/qualify.html

https://docs.snowflake.net/manuals/sql-reference/functions/row_number.html

Using `DISTINCT ON` in Snowflake

1 Answers