Using max() function to find max column value? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Using max() function to find max column value?

Author

Topic

rpieszak
Starting Member

11 Posts

Posted - 2004-05-20 : 12:11:27

Greetings all,
I understand the normal use for max(), to find the maximum value of a column. I'd like to find the maximum value of a row. For example, if I had this table...

MYTABLE
Date-------Col1---Col2---Col3
5/1/04-------45-----53-----65
5/2/04-------36-----41-----17
5/3/04-------65-----43-----25
5/4/04-------25-----76-----61

Now, I want the date and the maximum value of the other three columns. So, ideally if I used syntax something line this...

Select Date,Max(Col1,Col2,Col3) as myCol From MYTABLE

...my result set would look like this...

MYRESULT
Date-----myCol
5/1/04------65
5/2/04------41
5/3/04------65
5/4/04------76

Any ideas on how to do this?
Thanks, Ryan

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2004-05-20 : 12:34:58

[code]
select date,
case when col1 > col2 and col1 > col3 then col1
when col2 > col1 and col2 > col3 then col2
else col3 end
from t
group by date
[/code]

rpieszak
Starting Member

11 Posts

Posted - 2004-05-20 : 12:40:09

Thank you for your quick response. To expand on my request, I must tell you that there will actually be 24 columns (for hours per day). Your given syntax will work, but it will be very long, and probably not efficient for a query with this many columns. Are there any other ways? Or, will your syntax still be efficient for 24 columns?
Thanks!

bmanoj
Starting Member

13 Posts

Posted - 2004-05-20 : 13:07:06

Try this:

select MYTABLE.Date,
(select max(temp.mycolumn)
from (select MYTABLE.Col1 as mycolumn
union
select MYTABLE.Col2 as mycolumn
union
select MYTABLE.Col3 as mycolumn) temp) as myCol
from MYTABLE

if above works for Col1,Col2,Col3 then just modify query for rest of your columns.

HTH,
Manoj

rpieszak
Starting Member

11 Posts

Posted - 2004-05-20 : 13:17:58

Okay Manoj, if I wanted to add a where clause, say on Date, to limit the data, where would I add it?

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-05-20 : 13:28:46

This will be even more inefficient than writing the entire thing out or using unions, but it's shorter and easier to type. It would be OK for small result sets;

-- Create your own f_max function that takes 2 agruments
create function f_max(@a sql_variant, @b sql_variant) returns sql_variant AS
BEGIN
RETURN CASE when @a > @b THEN @a else @b END
END

Then you can use;

Select Date,dbo.f_max(Col4,dbo.f_max(Col3,dbo.f_max(Col2,Col1))) as myCol From MYTABLE

add more Coln references as necessary

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-20 : 13:29:01

May not be fastest but is probably the simplest

select dte, max(col)
from
(
select dte, col = col1 from tbl
union
select dte, col = col2 from tbl
union
select dte, col = col3 from tbl
union
select dte, col = col4 from tbl
) t
group by dte

You can generate the statement from the table structure or build it in dynamic sql if you don't want to type it in.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rpieszak
Starting Member

11 Posts

Posted - 2004-05-20 : 13:56:56

Thanks for all the suggestions. I used the function method because it was remarkably fast. Thanks again.

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-05-20 : 17:13:59

I have never found anyone who was excited to hear this advice, and yet I provide it again: Restructure you database. The fact that you are having to go to so much trouble to get what should be a simple answer is a big sign that there is trouble in the schema structure. And the fact that you have a series of columns (24 of them, to be exact) containing essentially the same kind of data is another sign of possible trouble. I urge you to fix this ASAP before it gets worse.

I know, I know... "I can't do that. It's not within the scope of my project." I understand. But I encourage you to raise the issue that the database is not structured properly and that a project should be created to fix that problem. I'm in the process of doing that for one of my clients right now. It took a bit of a fight to get them to agree, and it finally came down to them asking for what seemed to be a simple change and when I explained how outrageously complex it was going to be because of the structure, and then explained how I could take a query with 7 subqueries that when printed out took up 5 pages of paper and turn that into a single query about 3 lines long (using the correct structure) they finally agreed.

--------------------------------------------------------------
Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources