0

Normally if you want to find something across all columns you have to

select max(col1),max(col2),max(col3)...max(col99) from blah;

Is there a way to do this without naming every column manually?

  • No there is not. – mustaccio Aug 26 '19 at 22:26
1

https://mariadb.com/kb/en/library/procedure-analyse/ (Also works in MySQL.)

SELECT * FROM blah PROCEDURE ANALYSE();

For example, using a table of Canadian cities; notice the min and max values:

mysql> SELECT * FROM canada PROCEDURE ANALYSE()\G
*************************** 1. row ***************************
             Field_name: world.canada.id
              Min_value: 291724
              Max_value: 297207
             Min_length: 6
             Max_length: 6
       Empties_or_zeros: 0
                  Nulls: 0
Avg_value_or_avg_length: 294465.5000
                    Std: 1583.0944
      Optimal_fieldtype: MEDIUMINT(6) UNSIGNED NOT NULL
*************************** 2. row ***************************
             Field_name: world.canada.country
              Min_value: ca
              Max_value: ca
             Min_length: 2
             Max_length: 2
       Empties_or_zeros: 0
                  Nulls: 0
Avg_value_or_avg_length: 2.0000
                    Std: NULL
      Optimal_fieldtype: ENUM('ca') NOT NULL
*************************** 3. row ***************************
             Field_name: world.canada.ascii_city
              Min_value: 
              Max_value: zehner
             Min_length: 0
             Max_length: 42
       Empties_or_zeros: 1
                  Nulls: 0
Avg_value_or_avg_length: 10.0376
                    Std: NULL
      Optimal_fieldtype: VARCHAR(42) NOT NULL
*************************** 4. row ***************************
             Field_name: world.canada.city
              Min_value: (dup population)
              Max_value: Zehner
             Min_length: 1
             Max_length: 31
       Empties_or_zeros: 3
                  Nulls: 0
Avg_value_or_avg_length: 9.7861
                    Std: NULL
      Optimal_fieldtype: TINYTEXT NOT NULL
*************************** 5. row ***************************
             Field_name: world.canada.state
              Min_value: 
              Max_value: 14
             Min_length: 0
             Max_length: 2
       Empties_or_zeros: 1
                  Nulls: 0
Avg_value_or_avg_length: 1.9996
                    Std: NULL
      Optimal_fieldtype: ENUM('','01','02','03','04','05','07','08','09','10','11','12','13','14') NOT NULL
*************************** 6. row ***************************
             Field_name: world.canada.population
              Min_value: 540
              Max_value: 4612187
             Min_length: 3
             Max_length: 7
       Empties_or_zeros: 4771
                  Nulls: 0
Avg_value_or_avg_length: 4300.6415
                    Std: 85242.3200
      Optimal_fieldtype: MEDIUMINT(7) UNSIGNED NOT NULL
*************************** 7. row ***************************
             Field_name: world.canada.lat
              Min_value: 0
              Max_value: 82.4832992553711
             Min_length: 1
             Max_length: 7
       Empties_or_zeros: 1
                  Nulls: 0
Avg_value_or_avg_length: 50.09833726667297
                    Std: 5.212114845361912
      Optimal_fieldtype: FLOAT NOT NULL
*************************** 8. row ***************************
             Field_name: world.canada.lng
              Min_value: -140.98300170898438
              Max_value: 0
             Min_length: 1
             Max_length: 8
       Empties_or_zeros: 1
                  Nulls: 0
Avg_value_or_avg_length: -89.74846842687205
                    Std: 23.8526411452119
      Optimal_fieldtype: FLOAT NOT NULL
*************************** 9. row ***************************
             Field_name: world.canada.stripe
              Min_value: 0
              Max_value: 1190
             Min_length: 1
             Max_length: 4
       Empties_or_zeros: 1
                  Nulls: 0
Avg_value_or_avg_length: 966.6869
                    Std: 37.9899
      Optimal_fieldtype: SMALLINT(4) UNSIGNED NOT NULL
*************************** 10. row ***************************
             Field_name: world.canada.province
              Min_value: Alberta
              Max_value: Yukon
             Min_length: 5
             Max_length: 25
       Empties_or_zeros: 0
                  Nulls: 0
Avg_value_or_avg_length: 10.9220
                    Std: NULL
      Optimal_fieldtype: ENUM('Alberta','British Columbia','Manitoba','New Brunswick','Newfoundland and Labrador','Northwest Territories','Nova Scotia','Nunavut','Ontario','Prince Edward Island','Quebec','Saskatchewan','Yukon') NOT NULL
10 rows in set (0.04 sec)
  • Not what the OP is asking IMHO. – Vérace Aug 27 '19 at 3:21
  • @Vérace - Well, it does give a bunch of other stuff. And it won't analyze arbitrarily large tables. I suspect it will work for a 99-column table. – Rick James Aug 27 '19 at 3:31
  • @RickJames While this works for a few examples I listed, it is not globally helpful for every function. – cybernard Aug 28 '19 at 0:22
0

Use php or any other language to create the query and execute it.

//php 
$str ="";
for($i=1;$i<=99;$i++)
{
   $str.=", max(col".$i.") ";
}



$str = ltrim($str,",");
$query = "SELECT $str FROM table_name ";
  • 1
    col1 to 99 was just an example. However, I could use a mysqli query to obtain all the column names I suppose. – cybernard Aug 28 '19 at 0:24
  • Metaprogramming for the win. – Colin 't Hart Aug 28 '19 at 18:31
0

As long as you know the name of the table to query you can get a list of the columns stored in it by querying the INFORMATION_SCHEMA Columns Table

Read that list into a temporary table and count how many items you have in there. Now build a new SQL query by looping through them and adding them to your new query string.

I'm not at my own computer to get the exact syntax so here's some pseudo code without any declarations or error checking:


insert into ListOfColumns (ColumnName)
SELECT COLUMN_NAME
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE table_name = 'tbl_name'

Select @vNumberOfColumns = count(*) from ListOfColumns

@vNewQueryStart = 'SELECT '
@vNewQueryMiddle = ''
@vNewQueryEnd = ' FROM tbl_name;'

While @vNumberOfColumns > 0
BEGIN
    Select @vColumnName = ColumnName from ListOfColumns where RowID = NumberOfColumns

    NewQueryMiddle = CONCAT(NewQueryMiddle, 'max(',@vColumnName,')')
    IF (@vNumberOfColumns != 0) THEN
        @vNewQueryMiddle = CONCAT(CONCAT(@vNewQueryMiddle,',') --so you don't get a comma after the last column
    END IF

    SET @vNumberOfColumns = @vNumberOfColumns - 1;
END

@vFullNewQuery = CONCAT(@vNewQueryStart,@vNewQueryMiddle,@vNewQueryEnd)

PREPARE stmt FROM @vFullNewQuery;
EXECUTE stmt;
DEALLOCATE PREPARE stmt; 

It should at least give you an idea to run with.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.