My query returns a result like shown in the table. I would like to randomly pick an ID from the ID column and get all the rows having that ID. How can I do that in SnowFlake or SQL:

CodePudding user response：

There is a Snowflake function to return a fix number of "random" rows

WITH DATA AS (
select '1e3d' id,'NK25F4' postalcode,3214 some_value union all   
select '1e3d' id,'NK25F4' postalcode,3258 some_value union all 
select '1e3d' id,'NK25F4' postalcode,3354 some_value union all   
select '1f74' id,'NG2LK8' postalcode,5524 some_value union all  
select '1f74' id,'NG2LK8' postalcode,5548 some_value union all  
select '3e9a' id,'N6B7H4' postalcode,3694 some_value union all  
select '3e9a' id,'N6B7H4' postalcode,3325 some_value union all  
select '38e4' id,'N6C7H2' postalcode,3654 some_value )
SELECT * FROM DATA ,LATERAL (SELECT ID FROM DATA SAMPLE(2 ROWS)) I WHERE I.ID = DATA.ID