2

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 
   )
halfer
  • 19,824
  • 17
  • 99
  • 186
Learner
  • 8,379
  • 7
  • 44
  • 82

1 Answers1

6

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

Gokhan Atil
  • 9,278
  • 2
  • 11
  • 24
  • Thanks for the response , one doubt if the allowed id is empty how can i remove that – Learner Feb 13 '20 at 12:34
  • because i get two results after doing qualify but what happens one row doesn't have allowed_id so its empty value in that column so i need to remove that entire record. Basically is allowed_id is empty don't consider for Partition – Learner Feb 13 '20 at 12:38
  • and in the answer where is this 11,12 comes because i am trying to count , max and min of three different columns – Learner Feb 13 '20 at 12:39
  • and while merging if two records has same allowed_id but 1, 1 but the other columns for the first row doesn't have values and second row has values so basically the row which has values will be overwritten by blank values how can i avoid that – Learner Feb 13 '20 at 12:54
  • whats the use of giving = 1 – Learner Feb 13 '20 at 12:56
  • When the allowed_id is empty/null, it will be filtered out because of the WHERE condition. – Gokhan Atil Feb 13 '20 at 13:04
  • "=1" in "QUALIFY ROW_NUMBER() OVER (PARTITION BY allowed_id ORDER BY allowed_id) = 1", is to fetch only the first row. – Gokhan Atil Feb 13 '20 at 13:05
  • And about a row with blank values, you can add important columns to ORDER BY so blank ones will not be the first to return: ..... QUALIFY ROW_NUMBER() OVER (PARTITION BY allowed_id ORDER BY allowed_id, col2 ) = 1 ; – Gokhan Atil Feb 13 '20 at 13:08