High/Low Values in mulitiple columns

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 2000 Forums

SQL Server Development (2000)

High/Low Values in mulitiple columns

Author

Topic

jpiscit1
Posting Yak Master

130 Posts

Posted - 2004-02-02 : 08:23:23

Hello.

I am attempting to find the highest and lowest values in multiple columns with a given table. I'm familiar with the MAX/MIN functions but this only checks one column. How would I query 9 different columns to find the min and max values ? Each column type is numeric and represents the speeds of motors. Ultimately I would expect to end up with two values (Min and Max) for a given query.

Thanks in advance.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-02-02 : 08:31:43

select 'text desc of cola', min(cola), max(cola) from tablea
union
select 'text desc of colb', min(colb), max(colb) from tablea

etc....

jpiscit1
Posting Yak Master

130 Posts

Posted - 2004-02-02 : 09:08:00

Ok not exactly what I am looking for, but close.

Here's what I had:

Select Min(Agit_step1_speed_SP), Max(Agit_step1_speed_SP)
From tbl_3PrecipProduction
Where Campaign_no = 301834
Union
Select Min(Agit_step2_speed_SP), Max(Agit_step2_speed_SP)
From tbl_3PrecipProduction
Where Campaign_no = 301834
Union
Select Min(Agit_step3_speed_SP), Max(Agit_step3_speed_SP)
From tbl_3PrecipProduction
Where Campaign_no = 301834
Union
Select Min(Agit_step4_speed_SP), Max(Agit_step4_speed_SP)
From tbl_3PrecipProduction
Where Campaign_no = 301834
Union
Select Min(Agit_step5_speed_SP), Max(Agit_step5_speed_SP)
From tbl_3PrecipProduction
Where Campaign_no = 301834
Union
Select Min(Agit_step6_speed_SP), Max(Agit_step6_speed_SP)
From tbl_3PrecipProduction
Where Campaign_no = 301834
Union
Select Min(Agit_step7_speed_SP), Max(Agit_step7_speed_SP)
From tbl_3PrecipProduction
Where Campaign_no = 301834
Union
Select Min(Agit_step8_speed_SP), Max(Agit_step8_speed_SP)
From tbl_3PrecipProduction
Where Campaign_no = 301834
Union
Select Min(Agit_step9_speed_SP), Max(Agit_step9_speed_SP)
From tbl_3PrecipProduction
Where Campaign_no = 301834

Here were the Reults:

0 0
56 56
84 84

Here is what I was hoping for:
0
84

Where 0 is the min and 84 is the max. Also, Im betting someone has a more efficient way to run this code....ie less WHERE statements..

thoughts?

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-02-02 : 09:28:00

Is there any chance you could normalize the table in the database rather than using queries like this one to do so?

Then you could have 1 WHERE clause execution and one calculation for each aggregate needed for this query.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources