SQL: select max value... In row, across columns

Example:
col1      col2      col3      col4      col5      col6
1      0      0      3      0      2
1      1      2      0      0      2
1      2      2      4      3      1

I want to be able to create a column that takes the highest value amonst all these fields on their respective row, something like:
" select highest(col1, col2, col3, col4, col5, col6) as highestValue "

Anything out there like that? Keep in mind I actually have 13 columns.
   
dcmorrellAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
you will need a stored function for that, this version with 5 arguments:

as functions don't allow optional arguments, I would create a function with 15 arguments, and pass in your example 2 null values at the end.
create function dbo.Highest_05(@val1 sql_variant, @val2 sql_Variant, @val3 sql_variant, @val4 sql_variant, @val5 sql_Variant) returns sql_variant
as
begin
  declare @t table(data sql_Variant)
  declare @res sql_Variant
  insert into @t values(@val1)
  insert into @t values(@val2)
  insert into @t values(@val3)
  insert into @t values(@val4)
  insert into @t values(@val5)
  select @res = max(data) from @t
  return @res
end
go
select dbo.Highest_05(1,2,3,'A', null)
go
drop function dbo.Highest_05

Open in new window

appariCommented:
try this create a function and use it as follows, i used 4 params you can expand it

Create FUNCTION [dbo].[Greatest]
(
@val1 int,
@val2 int,
@val3 int,
@val4 int
)
RETURNS int
AS
BEGIN
      -- Declare the return variable here
      DECLARE @ResultVar int

      -- Add the T-SQL statements to compute the return value here
      SELECT @ResultVar = max(MyVal) From
            (Select @val1 MyVal
            union Select @val2
            union Select @val3
            union Select @val4) A

      -- Return the result of the function
      RETURN @ResultVar

END


Select Col1,Col2,Col3,Col4,
dbo.Greatest(Col1,Col2,Col3,Col4)
from dbo.TestTab
Shanmuga SundaramDirector of Software EngineeringCommented:
select max(col1),max(col2),max(col3),max(col4),max(col5),max(col6) from yourtablename
 
must help.
Ensure Business Longevity with As-A-Service

Using the as-a-service approach for your business model allows you to grow your revenue stream with new practice areas, without forcing you to part ways with existing clients just because they don’t fit the mold of your new service offerings.

Shanmuga SundaramDirector of Software EngineeringCommented:
sorry I misunderstood your need
vinurajrCommented:
Scott PletcherSenior DBACommented:
--SELECT dbo.GetMax(23, 45, 64, 22, 18, 224, 74, 138, 1, 98, 11, 86, 198)

--Naturally adjust data type to match what you actually need for your specific values


CREATE FUNCTION GetMax (
    @value01 int = NULL,  
    @value02 int = NULL,
    @value03 int = NULL,
    @value04 int = NULL,
    @value05 int = NULL,
    @value06 int = NULL,
    @value07 int = NULL,
    @value08 int = NULL,
    @value09 int = NULL,
    @value10 int = NULL,
    @value11 int = NULL,
    @value12 int = NULL,
    @value13 int = NULL
)
RETURNS int
AS
BEGIN
RETURN (
SELECT TOP 1 value
FROM (
    SELECT @value01 AS value UNION ALL
    SELECT @value02 UNION ALL
    SELECT @value03 UNION ALL
    SELECT @value04 UNION ALL
    SELECT @value05 UNION ALL
    SELECT @value06 UNION ALL
    SELECT @value07 UNION ALL
    SELECT @value08 UNION ALL
    SELECT @value09 UNION ALL
    SELECT @value10 UNION ALL
    SELECT @value11 UNION ALL
    SELECT @value12 UNION ALL
    SELECT @value13
) AS [values]
ORDER BY value DESC    
)
END --FUNCTION

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
Functions have fairly significant overhead anyway, so should always be written as efficiently as possible :-) .
dcmorrellAuthor Commented:
Works perfectly, thank you!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.