Max of multiple columns 2

[KPoggensee]

Say I have a SQL statement something like:

Select Max(Col1), Max(Col2), Max(Col3) From Table1

The results are;
Col1 Col2 Col3
3 8 5

I would like to return the Max of these columns so the only return is 8.

Any Suggestions?

Ken

 

[MarcLodge]

Hi Ken,
Feel sure there's a better way but

SELECT CASE
WHEN MAX(COL1) > MAX(COL2)
AND MAX(COL1 > MAX(COL3)
THEN MAX(COL1
WHEN MAX(COL2) > MAX(COL1)
AND MAX(COL2) > MAX(COL3)
THEN MAX(COL2)
WHEN MAX(COL3) > MAX(COL1)
AND MAX(COL3) > MAX(COL2)
THEN MAX(COL3)
END
FROM TABLE1

Will work (I think). Will give some more thought to see if anything else occurs.
Marc
FROM SYSIBM.SYSSTOGROUP

 

[swampBoogie]

select max(c)
from (select max(col1) c from table1
union all select max(col2) from table1
union all select max(col3) from table1)

 

[Golom]

Select
IIF (MAX(Col1) >= MAX(Col2),
IIF(MAX(Col1) >= MAX(Col3), MAX(Col1),
IIF (MAX(Col2) >= MAX(Col3), MAX(Col2), MAX(Col3)))) As [Max Value]

Looks like the real problem is that the data are not normalized and you should have the "Col1", "Col2" and "Col3" values in a second table so you could use

Select MAX(BCols) As [Max Value]
From Master As A INNER JOIN Columns As B ON A.Key=B.Key

 

[KPoggensee]

SwampBoggie/All,

I'm trying to go with your solution but I hid some of the underlying SQL to simplify it and can't seem to get it to fully work.

First of all I know the data structure is not optimal (If only you knew how far that rabbit hole goes) but this is an inherited project that already has a lot of stuff tied to it.

Anyways, here is the whole story. I am summing up the records individually (See SQL below) instead of Max() because of the bad data structure. I incorporated your unions and got one column that it looked like I could do a Select Max(c) FROM (...) on .

c
-----------
715
500
340
230

but when I try to add the
SELECT Max(c) FROM (...)
It says

'Server: Msg 170, Level 15, State 1, Line 71
Line 71: Incorrect syntax near ')'.

I hope I'm not being stupid but I quadruple checked the parentheses and I'm pretty sure it's right. I even put\removed parens in different places for about a fifteen minutes with no success

I was wondering if the way I'm adding the values would have an adverse effect on the 'Select Max(c) From (...)'

Here's the whole thing (Sorry so long, but you can see the agony that I went through so far


------------------------------------------------------------
SELECT Max(c) FROM
(
SELECT
COALESCE(SYSDBA.M_CE_PROFILE_APPROVED.FRONTAXLE_OEM1AMT, 0) +
COALESCE(SYSDBA.M_CE_PROFILE_APPROVED.SRA_OEM1AMT, 0) +
COALESCE(SYSDBA.M_CE_PROFILE_APPROVED.TRA_OEM1AMT, 0) +
COALESCE(SYSDBA.M_CE_PROFILE_APPROVED.CLUTCH_OEM1AMT, 0) +
COALESCE(SYSDBA.M_CE_PROFILE_APPROVED.TRANS_OEM1AMT, 0) +
COALESCE(SYSDBA.M_CE_PROFILE_APPROVED.DRIVE_OEM1AMT, 0) +
COALESCE(SYSDBA.M_CE_PROFILE_APPROVED.FBRAKE_OEM1AMT, 0) +
COALESCE(SYSDBA.M_CE_PROFILE_APPROVED.RBRAKE_OEM1AMT, 0) +
COALESCE(SYSDBA.M_CE_PROFILE_APPROVED.DRUMS_OEM1AMT, 0) +
COALESCE(SYSDBA.M_CE_PROFILE_APPROVED.AIRDRYER_OEM1AMT, 0) +
COALESCE(SYSDBA.M_CE_PROFILE_APPROVED.ASA_OEM1AMT, 0) +
COALESCE(SYSDBA.M_CE_PROFILE_APPROVED.ABS_OEM1AMT, 0) +
COALESCE(SYSDBA.M_CE_PROFILE_APPROVED.OTHER_OEM1AMT, 0) c
FROM SYSDBA.M_CE_PROFILE_APPROVED
WHERE SYSDBA.M_CE_PROFILE_APPROVED.M_CE_HeaderID = 'QVVUTA000CM2'

UNION ALL SELECT
COALESCE(SYSDBA.M_CE_PROFILE_APPROVED.FRONTAXLE_OEM2AMT, 0) +
COALESCE(SYSDBA.M_CE_PROFILE_APPROVED.SRA_OEM2AMT, 0) +
COALESCE(SYSDBA.M_CE_PROFILE_APPROVED.TRA_OEM2AMT, 0) +
COALESCE(SYSDBA.M_CE_PROFILE_APPROVED.CLUTCH_OEM2AMT, 0) +
COALESCE(SYSDBA.M_CE_PROFILE_APPROVED.TRANS_OEM2AMT, 0) +
COALESCE(SYSDBA.M_CE_PROFILE_APPROVED.DRIVE_OEM2AMT, 0) +
COALESCE(SYSDBA.M_CE_PROFILE_APPROVED.FBRAKE_OEM2AMT, 0) +
COALESCE(SYSDBA.M_CE_PROFILE_APPROVED.RBRAKE_OEM2AMT, 0) +
COALESCE(SYSDBA.M_CE_PROFILE_APPROVED.DRUMS_OEM2AMT, 0) +
COALESCE(SYSDBA.M_CE_PROFILE_APPROVED.AIRDRYER_OEM2AMT, 0) +
COALESCE(SYSDBA.M_CE_PROFILE_APPROVED.ASA_OEM2AMT, 0) +
COALESCE(SYSDBA.M_CE_PROFILE_APPROVED.ABS_OEM2AMT, 0) +
COALESCE(SYSDBA.M_CE_PROFILE_APPROVED.OTHER_OEM2AMT, 0) c
FROM SYSDBA.M_CE_PROFILE_APPROVED
WHERE SYSDBA.M_CE_PROFILE_APPROVED.M_CE_HeaderID = 'QVVUTA000CM2'

UNION ALL SELECT
COALESCE(SYSDBA.M_CE_PROFILE_APPROVED.FRONTAXLE_OEM3AMT, 0) +
COALESCE(SYSDBA.M_CE_PROFILE_APPROVED.SRA_OEM3AMT, 0) +
COALESCE(SYSDBA.M_CE_PROFILE_APPROVED.TRA_OEM3AMT, 0) +
COALESCE(SYSDBA.M_CE_PROFILE_APPROVED.CLUTCH_OEM3AMT, 0) +
COALESCE(SYSDBA.M_CE_PROFILE_APPROVED.TRANS_OEM3AMT, 0) +
COALESCE(SYSDBA.M_CE_PROFILE_APPROVED.DRIVE_OEM3AMT, 0) +
COALESCE(SYSDBA.M_CE_PROFILE_APPROVED.FBRAKE_OEM3AMT, 0) +
COALESCE(SYSDBA.M_CE_PROFILE_APPROVED.RBRAKE_OEM3AMT, 0) +
COALESCE(SYSDBA.M_CE_PROFILE_APPROVED.DRUMS_OEM3AMT, 0) +
COALESCE(SYSDBA.M_CE_PROFILE_APPROVED.AIRDRYER_OEM3AMT, 0) +
COALESCE(SYSDBA.M_CE_PROFILE_APPROVED.ASA_OEM3AMT, 0) +
COALESCE(SYSDBA.M_CE_PROFILE_APPROVED.ABS_OEM3AMT, 0) +
COALESCE(SYSDBA.M_CE_PROFILE_APPROVED.OTHER_OEM3AMT, 0) c
FROM SYSDBA.M_CE_PROFILE_APPROVED
WHERE SYSDBA.M_CE_PROFILE_APPROVED.M_CE_HeaderID = 'QVVUTA000CM2'

UNION ALL SELECT
COALESCE(SYSDBA.M_CE_PROFILE_APPROVED.FRONTAXLE_OEM4AMT, 0) +
COALESCE(SYSDBA.M_CE_PROFILE_APPROVED.SRA_OEM4AMT, 0) +
COALESCE(SYSDBA.M_CE_PROFILE_APPROVED.TRA_OEM4AMT, 0) +
COALESCE(SYSDBA.M_CE_PROFILE_APPROVED.CLUTCH_OEM4AMT, 0) +
COALESCE(SYSDBA.M_CE_PROFILE_APPROVED.TRANS_OEM4AMT, 0) +
COALESCE(SYSDBA.M_CE_PROFILE_APPROVED.DRIVE_OEM4AMT, 0) +
COALESCE(SYSDBA.M_CE_PROFILE_APPROVED.FBRAKE_OEM4AMT, 0) +
COALESCE(SYSDBA.M_CE_PROFILE_APPROVED.RBRAKE_OEM4AMT, 0) +
COALESCE(SYSDBA.M_CE_PROFILE_APPROVED.DRUMS_OEM4AMT, 0) +
COALESCE(SYSDBA.M_CE_PROFILE_APPROVED.AIRDRYER_OEM4AMT, 0) +
COALESCE(SYSDBA.M_CE_PROFILE_APPROVED.ASA_OEM4AMT, 0) +
COALESCE(SYSDBA.M_CE_PROFILE_APPROVED.ABS_OEM4AMT, 0) +
COALESCE(SYSDBA.M_CE_PROFILE_APPROVED.OTHER_OEM4AMT, 0) c
FROM SYSDBA.M_CE_PROFILE_APPROVED
WHERE SYSDBA.M_CE_PROFILE_APPROVED.M_CE_HeaderID = 'QVVUTA000CM2'

)
------------------------------------------------------------

With Select Max(c) FROM (...)
Server: Msg 170, Level 15, State 1, Line 71
Line 71: Incorrect syntax near ')'.

Just the (...)
c
-----------
715
500
0
0

(4 row(s) affected)

 

[dnoeth]

You just forgot to name the derived table, so just modify the last line to
&quot xx"

"the data structure is not optimal"
You're definitly right ;-)

Dieter