October 22nd, 2008, 02:33 PM
Select the smaller of two numbers
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
October 22nd, 2008, 02:53 PM
case when a < b then a else b end
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(A-B))
Larger of two numbers
(0.5*(A+B) + abs (A-B))
Comments on this post
October 25th, 2008, 10:14 AM
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
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...
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
unless a match whiz chimes in, i would simply use CASE expressions
Originally Posted by OneMoreStep
September 23rd, 2014, 03:12 PM
Why all this dicosions in sql form not about sql? solution in sql should be
declare @num as table
insert into @num
select 1, 5
select 2, 15
select 3, 3
select 4, 4
select 5, 5
select 6, 25
select minimumNumber = MIN(oneNum),
maximumNumber = MAX(onenum)