MySQL query for max() of all columns

Question

Hello, What is the correct way of retrieving maximum values of all columns in a table with a single query? Thanks.

Clarification: the same query should work on any table, i.e. the column names are not to be hard-coded into it.

Answer 1

SELECT max(col1) as max_col1, max(col2) as max_col2 FROM `table`;

Answer 2

I think (but would be happy to be shown wrong) that you have to know at least the number of columns in the table, but then you can do:

select max(c1),max(c2),max(c3),max(c4),max(c5)
from (
    select 1 c1, 1 c2, 1 c3, 1 c4, 1 c5 from dual where 0
    union all
    select * from arbitrary5columntable
) foo;

Obviously you lose any benefits of indexing.

Answer 3

You're going to have to do it in two steps - one to retrieve the structure of the table, followed by a second step to retrieve the max values for each

In php:

$table = "aTableName";
$columnsResult = mysql_query("SHOW COLUMNS FROM $table");

$maxValsSelect = "";
while ($aColumn = mysql_fetch_assoc($columnsResult)) {
  if (strlen($maxValsQuery) > 0) {
    //Seperator
    $maxValsSelect .= ", ";
  }  

  $maxValsSelect .= "MAX(" . $aColumn['Field'] . ") AS '" . $aColumn['Field'] "'";
} 

//Complete the query
$maxValsQuery = "SELECT $maxValsSelect FROM $table";
$maxValsReault = mysql_query($maxValsQuery);

//process the results....