### Thread: Select the smaller of two numbers

1. No Profile Picture
Registered User
Devshed Newbie (0 - 499 posts)

Join Date
Apr 2006
Posts
6
Rep Power
0

#### 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
2. No Profile Picture
Contributing User
Devshed Regular (2000 - 2499 posts)

Join Date
Jan 2003
Location
Paris Uppland
Posts
2,388
Rep Power
392

min:

Code:
`case when a < b then a else b end`
max:

Code:
`case when a > b then a else b end`
3. No Profile Picture
Registered User
Devshed Newbie (0 - 499 posts)

Join Date
Oct 2008
Posts
1
Rep Power
0
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))

• pabloj agrees
4. No Profile Picture
Registered User
Devshed Newbie (0 - 499 posts)

Join Date
Apr 2006
Posts
6
Rep Power
0

Thank you!
5. 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

6. No Profile Picture
Contributing User
Devshed Regular (2000 - 2499 posts)

Join Date
Jan 2003
Location
Paris Uppland
Posts
2,388
Rep Power
392

Standard SQL functions LEAST() and GREATEST()
Nope, they are not standard.
7. thanks, swampboogie, i thought they were, but you're right, they aren't
8. No Profile Picture
Registered User
Devshed Newbie (0 - 499 posts)

Join Date
Dec 2012
Posts
1
Rep Power
0

#### Formula given for smaller of two numbers was incorrect

Here are the correct formulae...

smaller: 0.5*((A+B)-abs(A-B))
larger: 0.5*((A+B)+abs(A-B))

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!
9. No Profile Picture
Registered User
Devshed Newbie (0 - 499 posts)

Join Date
Sep 2014
Posts
1
Rep Power
0
Ok, love it. How to do it with 3 numbers?
10. 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
11. No Profile Picture
Contributing User
Devshed Newbie (0 - 499 posts)

Join Date
Oct 2012
Posts
122
Rep Power
10
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```