October 22nd, 2008, 02:33 PM

Select the smaller of two numbers
Hi,
I wondered if there are mathematical MIN and MAX functions which select the smaller or larger of two numbers in Microsoft SQL? I found nothing in the manual.
For example: min(3,5)==3
max(1,2)==2
October 22nd, 2008, 02:53 PM

min:
Code:
case when a < b then a else b end
max:
Code:
case when a > b then a else b end
October 24th, 2008, 05:46 PM

without using specific SQL commands, as the min() and max() functions relate to columns rather than numbers. You could go with the "simple math trick" solution.
Wherever you want to find the smaller or larger, just insert one of these functions in place of any 'command'
Smaller of two numbers
(0.5*(A+B)  abs(AB))
Larger of two numbers
(0.5*(A+B) + abs (AB))
Comments on this post
October 25th, 2008, 10:14 AM

Thank you!
October 25th, 2008, 02:10 PM

the math is actually pretty clever
now axe him to do it with 3 numbers
see if he comes up with anything as simple as the Standard SQL functions LEAST() and GREATEST()
oh, wait, SQL Server doesn't support them
October 25th, 2008, 02:48 PM

Standard SQL functions LEAST() and GREATEST()
Nope, they are not standard.
October 25th, 2008, 04:44 PM

thanks, swampboogie, i thought they were, but you're right, they aren't
December 30th, 2012, 02:40 PM

Formula given for smaller of two numbers was incorrect
Here are the correct formulae...
smaller: 0.5*((A+B)abs(AB))
larger: 0.5*((A+B)+abs(AB))
The leftmost parenthesis was not in the correct place. In order for the formula to work correctly, .5 must be multiplied upon the entire expression. The reason each formula works is because the first expression (after being divided by 2 aka multiplied by .5) yields the average or midpoint between the two values. The second expression adds back half the difference between the two numbers to obtain the larger value or subtracts half the difference to obtain the smaller value.
This was a very clever formula once the misplaced parenthesis was corrected. I used the formula for vector analysis of electronic circuits where I needed to find the cosine of an angle or the cosine of 180 minus the angle, whichever angle was the smaller. So I greatly appreciate the person who posted this formula, even though it had a minor problem!
September 23rd, 2014, 02:12 PM

Ok, love it. How to do it with 3 numbers?
September 23rd, 2014, 02:33 PM

Originally Posted by OneMoreStep
Ok, love it. How to do it with 3 numbers?
unless a match whiz chimes in, i would simply use CASE expressions
September 23rd, 2014, 03:12 PM

Why all this dicosions in sql form not about sql? solution in sql should be
Code:
declare @num as table
(
recNum int,
oneNum decimal(18,2)
)
insert into @num
select 1, 5
union
select 2, 15
union
select 3, 3
union
select 4, 4
union
select 5, 5
union
select 6, 25
select minimumNumber = MIN(oneNum),
maximumNumber = MAX(onenum)
from @num