compare two columns for each row of resultset

Hi ,
I have a result set including two columns , I must compare two
columns
and put the larger number in another columns. I can use a loop ,
but I that's too slow on large datasets so I need something like
maxnum(Expr1,Expr2)
which can be applied on two numbers :
sample:
Expr1 Expr2 Expr3 must be :
10 15 15
70 32 70
12 40 40
50 25 50
any tricks ?
Best regards ,
Mehdy
--CREATE a FUNCTION (UDF) that accepts the comma delimited set of values, and
then parses them into a table variable, and returns the TOP 1.
For help, see:
http://realsqlguy.com/serendipity/archives/4-Parse-A-Delimited-String-Into-A-Table.html
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<bihoode@.gmail.com> wrote in message
news:1157576675.449860.135530@.p79g2000cwp.googlegroups.com...
> Hi ,
> I have a result set including two columns , I must compare two
> columns
> and put the larger number in another columns. I can use a loop ,
> but I that's too slow on large datasets so I need something like
> maxnum(Expr1,Expr2)
> which can be applied on two numbers :
> sample:
> Expr1 Expr2 Expr3 must be :
> 10 15 15
> 70 32 70
> 12 40 40
> 50 25 50
> any tricks ?
>
> Best regards ,
> Mehdy
> --
>|||-- Your Function is here
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[MaxOfTwoNumbers] (@.num1 int, @.num2 int)
RETURNS int AS
BEGIN
declare @.ReturnNum int
if @.num1 > @.num2
set @.ReturnNum = @.num1
else
set @.ReturnNum = @.num2
return @.ReturnNum
END
-- Simple function test
select dbo.MaxOfTwoNumbers (10,2)
-- So your ultimate update becomes:
Update yourtable set Expr3 = dbo.MaxOfTwoNumbers (Expr1,Expr2)
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||bihoode@.gmail.com wrote:
> Hi ,
> I have a result set including two columns , I must compare two
> columns
> and put the larger number in another columns. I can use a loop ,
> but I that's too slow on large datasets so I need something like
> maxnum(Expr1,Expr2)
> which can be applied on two numbers :
> sample:
> Expr1 Expr2 Expr3 must be :
> 10 15 15
> 70 32 70
> 12 40 40
> 50 25 50
> any tricks ?
>
> Best regards ,
> Mehdy
> --
>
SELECT
Col1,
Col2,
CASE WHEN Col1 > Col2 THEN Col1 ELSE Col2 END AS Col3
FROM MyTable
Tracy McKibben
MCDBA
http://www.realsqlguy.com