Max across multiple columns - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Max across multiple columns

Author

Topic

tinamiller1
Yak Posting Veteran

78 Posts

Posted - 2015-03-13 : 12:20:47

I have a table that has 565789 distinct rows and I am tring to get the maximum value across several columns. This is an example of my data:

mid value1 value2 value3 value4 value5 value6
111 .034 .005 .036 .010 .012 .002
222 .054 .032 .001 .002 .005 .006
333 .002 .004 .006 .001 .003 .087

The query I am trying is: and it states invalud from but not sure why


SELECT MID
(SELECT MAX(VAL) FROM 
(
SELECT A.VALUE1 FROM A UNION ALL
SELECT B.VALUE2 FROM B UNION ALL
SELECT C.VALUE3 FROM C UNION ALL
SELECT D.VALUE4 FROM D UNION ALL
SELECT E.VALUE5 FROM E UNION ALL
SELECT F.VALUE6 FROM F UNION ALL
) AS VAL
)AS MAXVAL
FROM MEMBER Z
INNER JOIN Z.MID = A.MID
INNER JOIN Z.MID = B.MID
INNER JOIN Z.MID = C.MID
INNER JOIN Z.MID = D.MID
INNER JOIN Z.MID = E.MID
INNER JOIN Z.MID = F.MID;

tina m miller

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-13 : 12:31:53

[code]
select mid, max(val)
from MEMBER
cross apply (
select value1 union all
select value2 union all
select value3 union all
select value4 union all
select value5 union all
select value6
) v(val)
group by mid
[/code]

tinamiller1
Yak Posting Veteran

78 Posts

Posted - 2015-03-13 : 13:52:14

Thanks. That was the ticket.

tina m miller

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources