CODEWITHSUNDEEP
snowflake-cloud-data-platformmin
gpak
gpak

Reputation: 21

SnowFlake - How to return the column that the least function value came from?

I am doing a Snowflake query where I get RANK from each column. Output below:

| Rank_A   | Rank B   |
| -------- | -------- |
| 1        | 3        |
| 2        | 4        |
| 5        | 4        |

Then I do the LEAST function to get the Minimum across those columns https://docs.snowflake.com/en/sql-reference/functions/least.html

Output below:

| Rank_A   | Rank B   | LEAST    | 
| -------- | -------- | -------- |
| 1        | 3        | 1        |
| 2        | 4        | 2        |
| 5        | 4        | 4        |

Lastly, (This is where I am not sure of) How can I make another column that returns what column the Least function came from - so what I am trying to return in this new column is like so:

Expected output below:

| Rank_A   | Rank B   | LEAST    | Column Name of Least Value |
| -------- | -------- | -------- | -------------------------- |
| 1        | 3        | 1        | Rank_A                     |  
| 2        | 4        | 2        | Rank_A                     |
| 5        | 4        | 4        | Rank_B                     |

I have no idea how I would go about this? any and all help would be greatly appreciated.

I do not know where to begin. I was contemplating doing this all in python before ingesting the data table?

Upvotes: 0

Views: 883

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521979

We can use a CASE expression along with the LEAST() function:

SELECT
    RANK_A,
    RANK_B,
    LEAST(Rank_A, Rank_B) AS LEAST,
    CASE WHEN RANK_A = LEAST(Rank_A, Rank_B)
         THEN 'RANK_A' ELSE 'RANK_B' END AS LEAST_VALUE
FROM yourTable;

In the event that RANK_A and RANK_B have the same value, the above query would arbitrarily return RANK_A as the column with the least value.

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 175924

You could use DECODE to get the first LEAST value column name:

SELECT *
    ,LEAST(Rank_A, Rank_B)
    ,DECODE(LEAST(Rank_A, Rank_B),
            Rank_A, 'Rank_A',
            Rank_B, 'Rank_B') AS column_name
FROM tab;

Output:

enter image description here

Upvotes: 1

Related Questions