CODEWITHSUNDEEP
mysqlsnowflake-cloud-data-platformdistinct
Mukul Kumar
Mukul Kumar

Reputation: 724

distinct of multiple columns snowflake

I have a table called XYZ having 3 columns adb1, adb2, adb3.(All columns are of number type)

I want a query to return a distinct number from all these 3 columns as a single row.

For example:- Table XYZ

adb1 adb2 adb3
11 12 13
12 24 25
78 25 13

Now the query should return one single column having distinct values from all these columns i.e.

Result column
11
12
13
24
25
78

Upvotes: 0

Views: 5321

Answers (2)

Rajat
Rajat

Reputation: 5803

An alternative using flatten. The idea is to create an array using your columns and then flatten them up in multiple rows

select distinct t2.value::integer as new_col --cast it to appropriate data type
from your_table t1, 
     lateral flatten(input=>[t1.adb1,t1.adb2,t1.adb3]) t2

Upvotes: 1

Dean Flinter
Dean Flinter

Reputation: 674

UNION should achieve this but can be computationally expensive depending on how many you are performing and the amount of data it needs to de-dupe

Something like:

SELECT adb1 FROM xyz
UNION
SELECT adb2 FROM xyz
UNION
SELECT adb3 FROM xyz

Upvotes: 0

Related Questions