MySQL query for showing only the max value length of each column

Question

I have a table with n columns and x rows.

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).

+--------+--------+----+
| column | length | id |
+--------+--------+----+
| a      |    123 |  1 |
| b      |    123 |  8 |
| c      |    123 |  6 |
| d      |    123 |  5 |
| e      |    123 |  3 |
+--------+--------+----+

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.

Something like this ? but then dynamicly for all columns:

SELECT MAX(LENGTH(`column_a`)) AS `length`, `id`
FROM `table` GROUP BY LENGTH(`column_a`) 
ORDER BY LENGTH(`column_a`) DESC LIMIT 1

Like having the next query with two columns added (the max length and the row id)

SHOW COLUMNS FROM `table`

Maybe using also this query to fetch the column names of the particular table:

SELECT `column_name` AS `column` 
FROM `information_schema`.`columns` 
WHERE `table_name` = 'table' 
AND `column_name` != 'id' 
ORDER BY `ordinal_position`

Nearly there (thanks to Bill)... (only have to specify 'table') but now how to run _SQL al the same run...

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`);

Solution 1

MySQL does not have any way to do what you want in a single query. At the time you prepare a query, all references to columns must be fixed.

The best you can do is to use one query against INFORMATION_SCHEMA to produce a new dynamic SQL query which gets the information you want.

I tested this against MySQL 5.5.16:

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_SCHEMA, TABLE_NAME) = ('test', 'tt') 
  AND COLUMN_NAME IN ('a', 'b', 'c');

This query produces the following output (I added newlines for the sake of posting here):

(SELECT 'a' AS `column`, LENGTH(`a`) AS `length`, id 
 FROM `test`.`tt` ORDER BY `length` DESC LIMIT 1) 
UNION ALL
(SELECT 'b' AS `column`, LENGTH(`b`) AS `length`, id 
 FROM `test`.`tt` ORDER BY `length` DESC LIMIT 1)
UNION ALL
(SELECT 'c' AS `column`, LENGTH(`c`) AS `length`, id 
 FROM `test`.`tt` ORDER BY `length` DESC LIMIT 1);

The generated query returns the following result in my test:

+--------+--------+----+
| column | length | id |
+--------+--------+----+
| a      |     10 |  2 | 
| b      |      9 |  1 | 
| c      |      6 |  2 | 
+--------+--------+----+

Note that this doesn't resolve ties. If more than one row has the same longest string, it will report only one id, chosen arbitrarily.

---

Re your extra question:

You must execute these as two separate queries. I said at the start of my answer that the column references must be fixed in the SQL string at the time you prepare your query. You can't discover the columns dynamically and also query their data in the same query.

---

Re your comment:

The longest string returned by GROUP_CONCAT() is 1024 by default, which is only enough to generate this SQL query for 9 columns. You can increase this limit:

SET group_concat_max_len = 1024*1024;

This increases the limit for the current database session only. You can use SET GLOBAL ... to change it for all sessions. If you want this to persist after restarting MySQL, set the value in your my.cnf file (no need to use SET GLOBAL in the config file).

Solution 2

Solving this will be a bit tricky, so I am not going to write the exact SQL, but here is the general idea:

1. Create a table variable to hold your results. It will have the same schema as you posted in your answer.

2. Create another table variable to hold all of the column names you want to pivot on. You just need a table with a name column.

3. Insert the column names into the temp table created in step 2:

   insert into @TempTable (ColumnName) select COLUMN_NAME from information_schema.columns where Table_Name = 'YourTable';

4. Using a cursor, loop through each of the columns in the column name table variable.

5. Within the loop, you are going to create and execute a dynamic SQL query, using the PREPARE STATEMENT function. Each iteration through the loop will insert one row into the result table for a single column name.

   PREPARE STMT FROM @query; EXECUTE STMT;

Here is a stab at the dynamic SQL that will need to be executed. The @columnName is the name of the variable from the loop.

declare @maxLength int;
select @maxLength =  max(length(@ColumnName)) maxlength from YourTable;
insert into @results (ColumnName, MaxLength, RowId)
select '@ColumnName', @maxLength, Id from YourTable where length(@ColumnName)) = @maxLength;

Alternatively, you can dump the data into Excel and use the pivot table feature :)

Comments

[Mike]

This is not exacly what I'm looking for. More like using (SHOW COLUMNS FROM table) with added two columns, one with the max length MAX(LENGTH()) of each column and the row id. I have multiple tables with each table 50-100 columns. So it must be dynamic.

[Eugen Rieck]

The important part is, that there is no such thing as reflection in MySQL - select field from (describe tablename) as reflec; will not work.

[Mike]

I've no idea what this query exactly does, but it's not dynamic, I've 50-100 columns in each table and making a query like this (for each column a query part) is not really an option. Sorry...

[Eugen Rieck]

As I pointed out in the first comment, this is impossible with MySQL: Neither "select field from (describe tablename)" nor "select field from (show fields from tablename)" will work, so in essence you cannot treat fieldnames as data, thus you can't query accross fieldnames and field values in the same query.

[Eugen Rieck]

Off topic: Why not use sanother tool to auto-create the queries? Why not use this external tool to auto create not only the queries, but also wrap the in stored procedures?

[Mike]

I have all column names using this query: SELECT column_name AS column FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'table' ORDER BY ordinal_position

[jmacinnes]

@Eugen: It is possible - you can execute dynamic SQL in MySql. Using information_schema, it is possible to create those dynamic SQL statements. dev.mysql.com/doc/refman/5.0/en/…

[Mike]

Thx, nearly there... See my addition at the bottom of my question.

[Mike]

More info from Bill: mysqlperformanceblog.com/2011/11/18/…

[Mike]

My builded query is longer than fits in _SQL, is there a way to mak _SQL type text first? Now it just cuts the query generation.