CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
Christopher Turnbull
Christopher Turnbull

Reputation: 1005

SQL - which column is the maximum?

Consider a simple table with integer values in col1,col2,col3

SELECT 
 "col1",
 "col2",
 "col3", 
 GREATEST("col1","col2","col3") AS "greatest_number" AS max_download 
FROM TEST_TBB_DEC_W_SPEEDS;

How can I return the field which tells me which column is the greatest one - instead of just the greatest value?

(I'm using the SQL version that comes w/ Snowflake, hence the use of GREATEST, but I'd be curious to see other answers that don't use this common extension too)

Upvotes: 1

Views: 366

Answers (3)

Lukasz Szozda
Lukasz Szozda

Reputation: 175556

Snowflake GREATEST/LEAST functions could work with ARRAY type. The first value is the column value, the second is the column name.

Sample data:

CREATE OR REPLACE TABLE tab
AS
SELECT 3 AS col1, 2 AS col2, 1 AS col3 UNION ALL 
SELECT 323,       45,        5655      UNION ALL 
SELECT 20,        30,        10;

Query:

SELECT GREATEST(ARRAY_CONSTRUCT(col1, 'col1'),
                ARRAY_CONSTRUCT(col2, 'col2'), 
                ARRAY_CONSTRUCT(col3, 'col3')) AS g
     ,g[0]::number  AS val
     ,g[1]::varchar AS column_name
FROM tab;

Output:

enter image description here

Upvotes: 2

MatBailie
MatBailie

Reputation: 86706

I don't have anything to test snowflake code on, but you could 'unpivot' the data (one row per value, instead of one column per value), then pick the top ranking row.

(Assumes that each source row has a unique id column.)

SELECT
  *
FROM
(
  SELECT
    *,
    RANK() OVER (PARTITION BY id ORDER BY col_value DESC)  AS ranking
  FROM
    TEST_TBB_DEC_W_SPEEDS
  UNPIVOT
  (
    col_value FOR col_name IN (col1, col2, col3, col4, ..., coln)
  )
)
  AS ranked
WHERE
  ranking = 1

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You can use a case expression:

select (case greatest(col1, col2, col3)
            when col1 then 'col1' when col2 then 'col2' when col3 then 'col3'
        end)

Upvotes: 1

Related Questions