CODEWITHSUNDEEP
mysqlmax
Mike
Mike

Reputation: 1943

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

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

Upvotes: 3

Views: 13985

Answers (4)

claudod
claudod

Reputation: 865

If I understood what you need, something like this should work (tested on Mysql 5.1):

SELECT  
    column, length, id 
FROM 
    (
     (SELECT 'a' AS column, CHAR_LENGTH(a) AS length, id 
      FROM yourtable 
      ORDER BY length DESC 
      LIMIT 1)
     UNION
     (SELECT 'b' AS column, CHAR_LENGTH(b) AS length, id 
      FROM yourtable 
      ORDER BY length DESC 
      LIMIT 1)
     UNION
     (SELECT 'c' AS column, CHAR_LENGTH(c) AS length, id 
      FROM yourtable 
      ORDER BY length DESC 
      LIMIT 1)
     UNION 
     (...)
) T

Upvotes: 1

Bill Karwin
Bill Karwin

Reputation: 562280

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

Upvotes: 5

jmacinnes
jmacinnes

Reputation: 1609

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

Upvotes: 1

Eugen Rieck
Eugen Rieck

Reputation: 65274

Try this:

SELECT
    length(concat('',a)) as maxlen,
    group_concat(if (length(concat('',a))=@maxlen_a,a,null)) as maxlenval
FROM
    (select @maxlen_a:=max(length(concat('',a))) from tablename) as aview,
    tablename
GROUP BY maxlen 
HAVING maxlenval IS NOT null

UNION

SELECT
    length(concat('',b)) as maxlen,
    group_concat(if (length(concat('',b))=@maxlen_b,b,null)) as maxlenval
FROM
    (select @maxlen_b:=max(length(concat('',b))) from tablename) as bview,
    tablename
GROUP BY maxlen 
HAVING maxlenval IS NOT null

... and so on for every column.

This also solves the problem of two different values of same length

Upvotes: 0

Related Questions