Get MAX from string with column name (SQL)
Sorry if my quest is simple, but I spent one day searching the web and still can't figure out how to solve it:
I have a table like:
userId A B C D E
1 5 0 2 3 2
2 3 2 0 7 3
And I need each MAX for each row with the column name:
userId MAX
1 A
2 D
Any ideas will be highly appreciated! Thank you! I am using Google Big Query, so my capabilities vary in MySQL form as I understand it, but I will try to translate if you have any ideas in MySQL.
+3
source to share
1 answer
You can use GREATEST
:
SELECT userid, CASE GREATEST(A,B,C,D,E)
WHEN A THEN 'A'
WHEN B THEN 'B'
WHEN C THEN 'C'
WHEN D THEN 'D'
WHEN E THEN 'E'
END AS MAX
FROM TableName
Result:
userId MAX
1 A
2 D
With a sample table, it looks like this:
SELECT userid, CASE GREATEST(A,B,C,D,E)
WHEN A THEN 'A'
WHEN B THEN 'B'
WHEN C THEN 'C'
WHEN D THEN 'D'
WHEN E THEN 'E'
END AS MAX
from (select 1 as userId, 5 as A, 0 as B, 2 as C, 3 as D, 2 as E),
(select 2 as userId, 3 as A, 2 as B, 0 as C, 7 as D, 3 as E)
+7
source to share