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

dcmorrell
dcmorrell used Ask the Experts™
on
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.
   
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
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

Commented:
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 Engineering

Commented:
select max(col1),max(col2),max(col3),max(col4),max(col5),max(col6) from yourtablename
 
must help.
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Shanmuga SundaramDirector of Software Engineering

Commented:
sorry I misunderstood your need
Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
--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
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
Functions have fairly significant overhead anyway, so should always be written as efficiently as possible :-) .

Author

Commented:
Works perfectly, thank you!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial