Selecting max values from multiple columns

Hello everyone,

I originally posted this on devshed. But I noticed that it is dead silent over there. I am not sure whether anything would happen over there. So my apologies for the crosspost.

I was asked to come up with a solution for a SELECT on multiple columns. I have not seen the database, so I don’t know whether it is structurally faulty or could be optimized in one way or another. Anyway, the situation is as follows:
in the first scenario there is a table with multiple columns that hold numeric data. The query ought to return the max value of each mentioned column just like

Code:

SELECT MAX(col1), MAX(col2), … , MAX(coln) FROM table

As there are many (I was told 200) columns this would require to state each column in the MAX(colx) part of the statement.
I would imagine there is a way of retrieving the column names and then looping through the result. Thus saving to state each and every column name. All the solutions I have found or thought of do just that.

And as if that was not enough. Scenario 2 and 3: What if there were also mutliple tables or multiple databases with the same task to find the max values?

Thank you for every suggestion

Step #1: Tell us what database engine you’re using.

hey, i remember devshed, it was pretty good for a while

there is, google INFORMATION_SCHEMA

my advice is not to try to automate this, just pull the column names and reformat them into a SELECT statement in a text editor as a one off task

1 Like

As this came up during a conversation on MySQL I have to assume that this is the engine which is used.

Hello r937,

I am so delighted to see you round here. Your answers on my few questions on devshed were always a great help.

I am already able to pull the column names from the INFORMATON_SCHEMA:
SELECT column_name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = ‘table’
ORDER BY ordinal_position;

In other cases I am building my query in Excel concatenating the various values. I was hoping that there could be a more elegant solution. But you are right, if it is prepared once, it can easily be reused once the need arises.

Thanks once more

(1)If same table then try following query:

SELECT 
   id, 
   (SELECT MAX(MaxValue)
      FROM (VALUES (col1),(col2),(col3)) AS Value(MaxValue)) 
   AS MaxValue
FROM Table1

(2)If you are having Two tables as follows:

Table1::

`col1` `col2`
11       0
22       100
33       10
44       0
55       25

Table2::

`col1` `col2`
11	     100
22	     80	
33	     25

Then by following Query max from two tables will be obtained

SELECT  col1,
        MAX(col2) AS [col2] FROM ( SELECT  col1,col2  FROM    Table1 UNION
      SELECT  col1, col2   FROM    Table2 ) u
GROUP BY col1
ORDER BY col1

Using Postgresql there is a function “greatest”. I suppose there is similar functions in other databases:

SELECT greatest(max(col1),max(col2),max(col3)) FROM table

or you can use CTE to make a list

WITH list AS (
SELECT
(SELECT max(col1) FROM table1),
(SELECT max(col1) FROM table2),
(select max(col1) FROM table3))
SELECT max(col1) FROM list