MySQL greatest value in row?

Question

Hi! I'm using MySQL with PHP. This is like my table: (I'm using 3 values, but there are more)

id | 1 | 2 | 3
---+---+---+----
1  | 3 |12 |-29
2  | 5 |8  |8
3  | 99|7  |NULL

I need to get the greatest value's column name in a certain row. It should get:

id | maxcol
---+-------
1  |   2
2  |   2
3  |   1

Are there any queries that will do this? I've been trying, but I can't get it to work right.

Answer 1

Are you looking for something like the GREATEST function? For example:

SELECT id, GREATEST(col1, col2, col3)
    FROM tbl
    WHERE ...

Combine it with a CASE statement to get column names:

SELECT id, CASE GREATEST(COALESCE(`1`, -2147483646), COALESCE(`2`, -2147483646), COALESCE(`3`, -2147483646))
         WHEN `1` THEN 1
         WHEN `2` THEN 2
         WHEN `3` THEN 3
         ELSE 0
      END AS maxcol
    FROM tbl
    WHERE ...

It's not pretty. You'd do better to follow Bill Karwin's suggestion and normalize, or simply take care of this in PHP.

function findcol($cmp, $arr, $cols=Null) {
   if (is_null($cols)) {
      $cols = array_keys($arr);
   }
   $name = array_shift($cols);
   foreach ($cols as $col) {
       if (call_user_func($cmp, $arr[$name], $arr[$col])) {
           $name = $col;
       }
   }
   return $name;
}

function maxcol($arr, $cols=Null) {
   return findcol(create_function('$a, $b', 'return $a < $b;'), $arr, $cols);
}

Answer 2

The short answer is that there is no simple means to do this via a query. You would need to transpose your data and then determine the largest value that way. So something like:

Select Id, ColumnName, Value
From    (
        Select '1' As ColumnName, Id, [1] As Value
        From Table
        Union All
        Select '2', Id, [2]
        From Table
        Union All
        Select '3', Id, [3]
        From Table
        ) As Z
Where Exists(
            Select 1
            From    (
                    Select '1' As ColumnName, Id, [1] As Value
                    From Table
                    Union All
                    Select '2', Id, [2]
                    From Table
                    Union All
                    Select '3', Id, [3]
                    From Table
                    ) As Z2
            Where Z2.Id = Z.Id
            Group By Z2.Id
            Having Max(Z2.Value) = Z.Value
            )
Order By Id

This solution depends on a fixed set of columns where you basically name the columns in the UNION ALL queries. In addition, if you have two columns with identical values for the same Id, you will get duplicate rows.

Answer 3

This query will return the max value regardless of NULLs

SELECT MAX(value)
FROM
(SELECT 1 column_no, col1 value
FROM anotherunamedtable
UNION ALL
SELECT 2, col2
FROM anotherunamedtable
UNION ALL
SELECT 3, col3
FROM anotherunamedtable) t

If you really need the column number then

SELECT id,
       (SELECT column_no
       FROM
              (SELECT 1 column_no, col1 value
              FROM anotherunamedtable
              WHERE id = t.id
              UNION ALL
              SELECT 2, col2
              FROM anotherunamedtable
              WHERE id = t.id
              UNION ALL
              SELECT 3, col3
              FROM anotherunamedtable
              WHERE id = t.id) s
        ORDER BY max_value DESC
        LIMIT 1)) as column_no
FROM anotherunamedtable t

But I think that the last query might perform exceptionally horrible. (Queries are untested)

Answer 4

This is a great example of the way normalization helps make query design easier. In First Normal Form, you would create another table so all the values would be in one column, on separate rows.

Since you have used repeating groups to store your values across three columns, you can find the column with the greatest value this way:

SELECT id, IF(col1>col2 AND col1>col3, 'col1', IF(col2>col3, 'col2', 'col3')) 
  AS column_with_greatest_value
FROM mytable;

Answer 5

In the php side, you could do something like this:

foreach ($rows as $key => $row) {
  $bestCol = $best = -99999;
  foreach ($row as $col => $value) {
    if ($col == 'id') continue; // skip ID column
    if ($value > $best) {
      $bestcol = $col;
      $best = $value;
    }
  }
  $rows[$key]['best'] = $bestCol;
}

Or something similar...

Answer 6

Forests and trees, here's a trivial and fastest solution (providing I didn't fumble); the expression simply looks for the largest column in the row

SELECT id,
       CASE COALESCE(col1, -2147483648) >= COALESCE(col2, -2147483648)
       WHEN 
           CASE COALESCE(col2, -2147483648) >= COALESCE(col3, -2147483648)
           WHEN true THEN 1
           ELSE 
                CASE COALESCE(col1, -2147483648) >= COALESCE(col3, -2147483648)
                WHEN true THEN 1
                ELSE 3
                END 
           END 
       ELSE 
           CASE COALESCE(col2, -2147483648) >= COALESCE(col3, -2147483648)
           WHEN true 2
           ELSE 3
           END 
       END 
FROM table t

a version with IF() would maybe be more readable, but the above should perform a bit better To deal with NULLS an INT value with minimum of -2147483648 was assumed, the expression could be rewritten to deal explicitly with nulls but would have to branch into 8 different cases and is left as an exercise for the OP.