Selecting max values from multiple columns

[ollie1]

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

[m_hutley]

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

[r937]

@ollie1

I originally posted this on devshed. But I noticed that it is dead silent over there.

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

@ollie1

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.

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

[ollie1]

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

[ollie1]

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

[Sherin]

@ollie1

tables

(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

[sibertius]

@Sherin

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

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