Highest number in a row

[andyiain]

Hi

Is there any way to get the highest number in a row. If it was in a column I could just use max but how can I find out what the largest number for a record is across several columns.

Any ideas kind people?

Andy

 

[mrdenny]

If working with a single row drop the values into a temp table and use max.

You can also use the UNPIVOT command to turn the columns into a single column which would allow you to then use the MAX function.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[andyiain]

Thanks for the reponse but alas I'm on sql 2000 (at least for the next couple of months) and it's multiple rows that are constantly updated.

I guess a lot of case statements may do the job but it seems to me to be an ugly way of doing things. I currently have four columns to check the highest number from and that may increase by a couple so any case statements would be tricky to manage.

 

[r937]

you could use the Standard SQL GREATEST function...

... but alas, SQL Server she no supports it ;-)


finding the largest value across columns means that the columns are somehow similar (they would at least have to be from the same domain)

this suggests that the table may not be properly normalized

you might want to "unpivot" the design



r937.com | rudy.ca

 

[RyanEK]

Hi,

Say your table has an ID field with 3 columns, the following will give you the column with the highest value for each row.

select id, max( val ) as MaxVal
from (
select id, col,
 case col
   when 1 then [Column1]
   when 2 then [Column2]
   when 3 then [Column3]
   end as val
from [TABLE]
cross join ( 
  select 1 as col union all
  select 2 union all
  select 3 
) As s
) as a
group by id

Ryan

 

[JoeAtWork]

I agree with r937, this is probably a normalization issue, especially since the OP is saying "more columns may be added".

Each of those columns should probably be in a row of a related table, then you could use the standard MAX operator.

Joe Schwarz
Custom Software Developer

http://www.plandev.net