CODEWITHSUNDEEP
sqlgoogle-bigquery
Taichi
Taichi

Reputation: 2587

How to get max value of column values in a record ? (BigQuery)

I wanna get max value of each rows, not max value of a field. For example, when I have a sample_table like this:

sample_table

|col1|col2|col3|
|--------------|
| 1  | 0  | 0  |
| 0  | 2  | 0  |
| 2  | 0  | 0  |
| 0  | 0  | 3  |

And the query and result I want is something like this:

query

SELECT SOME_GET_MAX_VAL_FUNC(col1, col2, col3) AS max_val FROM sample_table;

result

|max_val|
|-------|
|   1   |
|   2   |
|   2   |
|   3   |

I want some solution to replace SOME_GET_MAX_VAL_FUNC . If it is nearly impossible in SQL, why is it? (I'm new to SQL and BigQuery)

note

Upvotes: 3

Views: 13416

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172994

The number of cols maybe very big, like col1, col2, ... col200

having above in mind - below is optimal solution (BigQuery Standard SQL) which does not require explicit typing of all columns names like in GREATEST(col1, col2, col3, ..., col200) 

#standardSQL
SELECT *, 
  (SELECT MAX(value) FROM 
    UNNEST(REGEXP_EXTRACT_ALL(TO_JSON_STRING(t), r':([^,}]+)')) value
  ) max_val
FROM `project.dataset.table` t

If to apply to smple_table in your question result will be 

Row     col1    col2    col3    max_val  
1       1       0       0       1    
2       0       2       0       2    
3       2       0       0       2    
4       0       0       3       3

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You want GREATEST :

SELECT GREATEST(col1, col2, col3) 
FROM table t;

Upvotes: 13

Related Questions