2

I have test_scores table with following fields:

Table schema:

  id (number)
  score1  (number)
  score2  (number)
  score3  (number)
  score4  (number)

Sample data:

id score1 score2 score3 score4
1  10     05      30    50
2  05     15      10    00
3  25     10      05    15

Expected result set:

id col_name col_value
1  score4   50
2  score2   15
3  score1   25

What is a good SQL for this?(I am using MySQL.)

Original requirement didn't include the col names in the rowset. I was able to get the result using following SQL:

SELECT   A.id, MAX(A.score) AS max_score
FROM     (
            SELECT id, score1 as score FROM test_scores UNION
            SELECT id, score2 as score FROM test_scores UNION
            SELECT id, score3 as score FROM test_scores UNION
            SELECT id, score4 as score FROM test_scores
          ) AS A
GROUP BY A.id

But with the introduction of col_name in the result-set I am stumped..

Harish Shetty
  • 64,083
  • 21
  • 152
  • 198

4 Answers4

6

I solved this problem using the following SQL:

SELECT id, GREATEST(score1, score3, score3, score4) AS col_value,
       CASE GREATEST(score1, score3, score3, score4) 
         WHEN score1 THEN 'score1'
         WHEN score2 THEN 'score2'
         WHEN score3 THEN 'score3'
         WHEN score4 THEN 'score4'
       END AS col_name
FROM test_scores

Please let me know if there is a better solution.
Harish Shetty
  • 64,083
  • 21
  • 152
  • 198
2

For the col_value, you'll want to use GREATEST().

As for the col_name, you could do it like this, but it's hardly elegant:

SELECT id,
       IF(score1 = col_value, 'score1',
       IF(score2 = col_value, 'score2',
       IF(score3 = col_value, 'score3', 'score4'))) AS col_name,
       col_value
FROM (
    SELECT *, 
           GREATEST(score1, score2, score3, score4) AS col_value
    FROM test_scores
) AS helper
Sebastian Paaske Tørholm
  • 49,493
  • 11
  • 100
  • 118
1

Part of the solution (col_value) could be SQL MAX of multiple columns?.

Community
  • 1
  • 1
mkj
  • 2,761
  • 5
  • 24
  • 28
1

I much prefer the readability of using a case statement for this, but thought I'd post this just for kicks.

select GREATEST(score1, score2, score3, score4)
     , ELT(FIELD(GREATEST(score1, score2, score3, score4), score1, score2, score3, score4), 'score1', 'score2', 'score3', 'score4')
from (
    select 11 score1, 6 score2, 7 score3, 8 score4
) t
goat
  • 31,486
  • 7
  • 73
  • 96