Select the biggest value

Question

Post reply

Select the biggest value

sql_2005_fan

SSCommitted

Points: 1871
More actions
June 20, 2006 at 9:30 am

#153047

I have two variables @a and @B.And I wanted to select largest of the two values.
For example if @a =2 and @b-2=3
THen I need to select
SET @C=@b
Say if @a=10 and @b-2 =1
SELECT @C=@a
IS there any built in function to select the largest of the two values like ISNULL for null value.
Thanks.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

PW-201837 SSC-Insane Points: 20805 More actions · Answer 1

You implement If .. Then .. Else type logic in T-SQL using CASE ... WHEN:

SELECT @C = CASE WHEN @a > @b-2 THEN @a ELSE @b-2 END

sql_2005_fan SSCommitted Points: 1871 More actions · Answer 2

Thanks for your quick response and infact I was wondering if there is any built function to do the same instead of case statement.

Thanks.

stax68 SSChampion Points: 11711 More actions · Answer 3

nope.

If like me you are CASE-averse, you can do it with arithmetic, but I don't see any advantage, except the satisfaction of not using CASE, and handling NULLs (but a minor change to the ad-hoc version would do that too, of course)

create

function dbo.maxoftwo (@1 int, @2 int)

returns

int

as

begin

declare @i int

select @i = (@1+@2+abs(@1-@2))/2

return

@i

end

go

select

dbo.maxoftwo(256789,13728)

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

Jeff Moden SSC Guru Points: 1004279 More actions · Answer 4

Tim,

Do you have a "MinOfTwo" function for a fellow "CASE Adverse" compatriot?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004279 More actions · Answer 5

Never mind Well, duh!!!

select @i = (@1+@2-abs(@1-@2))/2

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sergiy SSC Guru Points: 110208 More actions · Answer 6

There is an inbuilt function for that.

It's "MAX". It's strange you did not know about that.

Actually TSQL is a language to deal with databases.

And databases hold all values in tables.

Deal with it.

Don't declare variables, keep values in tables - and you'll get happy life without problems.

DECLARE @Table TABLE (Val int)

INSERT INTO @Table (VAL)

SELECT @a

UNION

SELECT @b-2

SELECT MAX(VAL) from @Table

Or, without declaring table:

SELECT MAX(VAL)

from (

SELECT @a VAL

UNION

SELECT @b-2

) T

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004279 More actions · Answer 7

Yep... could be done that way and, as usual, is very easy and yet creative on your part. I was thinking something similar but I was really intrigued by the math behind the formula that Tim posted. Seems so simple...

Tim's formula is only good for two values unless you cascade the formula. Your idea is much better than CASE or the MaxOfTwo formula if you have more than two values to contend with. I'm thinking function, here...

Thanks, Serqiy... always a pleasure bouncing ideas around with you.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sergiy SSC Guru Points: 110208 More actions · Answer 8

You always welcome, Jeff.

When I've been asked (or I am asking ) for something what is not there I start to search not for a solution but for a reason why it's not there. And in 99% of cases there is a strong reason.

Almost everytime that reason brings me easy and clean solution, not always that I was asked for, but always nice and effective.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004279 More actions · Answer 9

It's too bad that functions don't work like sprocs do when it comes to defaulting inputs so that we could make this function a little wider without having to insert 'DEFAULT' as a parameter for missing parameters...

Anyway, I can see lot's of things to do with this and, over millions of records, might actually be faster than CASE. Still direct formulas without using a function are pretty cool, too. Some day, I test a bunch of the methods... case, direct formula, union select, etc.

Here's your idea wrapped in a function for convenience.

CREATE FUNCTION dbo.fnMaxOf2Dates

(

@Date01 DATETIME,

@Date02 DATETIME

)

RETURNS DATETIME

AS

BEGIN

RETURN (SELECT MAX(d.Val)

FROM (SELECT @Date01 AS Val

UNION ALL

SELECT @Date02 AS Val)d )

END

SELECT dbo.fnMaxOf2Dates('10/12/2000','10/11/2000')

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sergiy SSC Guru Points: 110208 More actions · Answer 10

Jeff, you don't need any special function.

I'm sure you've got our favourite "split" function.

Concatenate as many values as you need (starting from no values) into delimited string and select MAX from resultset of that function:

select MAX(convert(datetime, Val, 120))

FROM dbo.SplitString('2000-10-12, 2000-10-11, 2000-10-10', ',')

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004279 More actions · Answer 11

Ok... now I get to accuse you of reading minds... My only distain for that is if you are comparing two or more columns of dates, you would have to convert and concatenate the columns into a single string... there is some overhead in doing that. For a second, I considered a correleated sub-query but that almost always too slow. I'm thinking that a dedicated function using your UNION SELECT with the right number of parameters would offer the greatest performance.

Or... maybe I need more coffee before I do anything else with this

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)