title: MySQL query for showing only the max value length of each column
body: <p>I have a table with n columns and x rows.</p>
  
  <p>I want to make a query to show a table with all columns and the max length value of that column (and the row id of that row with the max value length).</p>
  
  <pre><code>+--------+--------+----+
  | column | length | id |
  +--------+--------+----+
  | a      |    123 |  1 |
  | b      |    123 |  8 |
  | c      |    123 |  6 |
  | d      |    123 |  5 |
  | e      |    123 |  3 |
  +--------+--------+----+
  </code></pre>
  
  <p>a, b, c, etc. are the column names and length is the max length in that column and id is the row with the max value length of that column.</p>
  
  <p>Something like this ? but then dynamicly for all columns:</p>
  
  <pre><code>SELECT MAX(LENGTH(`column_a`)) AS `length`, `id`
  FROM `table` GROUP BY LENGTH(`column_a`) 
  ORDER BY LENGTH(`column_a`) DESC LIMIT 1
  </code></pre>
  
  <p>Like having the next query with two columns added (the max length and the row id)</p>
  
  <pre><code>SHOW COLUMNS FROM `table`
  </code></pre>
  
  <p>Maybe using also this query to fetch the column names of the particular table:</p>
  
  <pre><code>SELECT `column_name` AS `column` 
  FROM `information_schema`.`columns` 
  WHERE `table_name` = 'table' 
  AND `column_name` != 'id' 
  ORDER BY `ordinal_position`
  </code></pre>
  
  <p>Nearly there (thanks to Bill)... (only have to specify 'table') but now how to run _SQL al the same run...</p>
  
  <pre><code>SELECT CONCAT(GROUP_CONCAT(CONCAT('(SELECT \'', `column_name`,'\' AS `column`, LENGTH(`', `column_name`,'`) AS `length`, id ', 'FROM `', `table_schema`,'`.`', `table_name`,'` ORDER BY `length` DESC LIMIT 1)') SEPARATOR ' UNION ALL '), ';') AS _SQL
  FROM `information_schema`.`columns` 
  WHERE `table_name` = 'table' 
    AND `column_name` IN (
      SELECT `column_name`
      FROM `information_schema`.`columns` 
      WHERE `table_name` = 'table' 
      AND `column_name` != 'id' 
      ORDER BY `ordinal_position`);
  </code></pre>