Finding Maximum value in a row of 24 fields

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

SQL Server 2005 Forums

Transact-SQL (2005)

Finding Maximum value in a row of 24 fields

Author

Topic

gs
Starting Member

14 Posts

Posted - 2008-01-04 : 20:09:35

Hello,

I have a table load which has load value for each hour.ie load_1,load_2...load_24... I want to find the max value between the 24 hourly loads and assign it to a variable say load_max...

Format of table
load_ID load_1 load_2 load_3 load_4 load_5 load_6...... load_24
1 2 4 5 6 7 8 23 56 44 22 64 33 67 24 345 34 75 57 24 23 24 24 66 789

These are the 24 load values with the load _id
I have lots of rows with load_id starting from 1- 100
Output should be to display the load_Id,load_max, load_min for each row...(after comparing the 24 loads with each other)
How can I do it with sql server.

Thanks

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-01-04 : 21:10:38

The link below explains how to do what you want.

MIN/MAX Across Multiple Columns
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=86906

CODO ERGO SUM

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-01-05 : 04:58:16

This might be easier to interprit



/*
--Creates a tmp data source
select *
into #tmp
From
(
Select 1 as loadID,7 as load_1, 1 as load_2, 3 as load_3, 9 as load_4 Union All
select 2,66,4,22,33 Union All
select 3,2,41,2,3 Union All
select 4,77,141,32,13 Union All
select 5,23,411,23,113 
) a

*/


select loadID,(Select max(aa.mycol) from 
(
Select a.Load_1 as MyCol Union All
Select a.Load_2 Union All
Select a.Load_3 Union All
Select a.Load_4 
) aa) as MaxValue
from 
#Tmp a

georgev
Posting Yak Master

122 Posts

Posted - 2008-01-05 : 21:07:45

This would be easy if the data was normalised ;)
[CODE]
SELECT load_id
, Max(load) As [maxLoad]
, Min(load) As [minLoad]
FROM loads
GROUP
BY load_id
[/CODE]

George
<3Engaged!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources