dcmorrell
asked on
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.
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.
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,Col 3,Col4)
from dbo.TestTab
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,Col
from dbo.TestTab
select max(col1),max(col2),max(co l3),max(co l4),max(co l5),max(co l6) from yourtablename
must help.
must help.
sorry I misunderstood your need
this link will give how u can make use of nest the functions
<http://articles.techrepublic.com.com/5100-10878_11-6162188.html#>
<http://techrepublic.com.com/html/tr/sidebars/6162188-0.html>
<http://articles.techrepublic.com.com/5100-10878_11-6162188.html#>
<http://techrepublic.com.com/html/tr/sidebars/6162188-0.html>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Functions have fairly significant overhead anyway, so should always be written as efficiently as possible :-) .
ASKER
Works perfectly, thank you!
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.
Open in new window