Forums: » Register « |  Free Tools |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support |

New Free Tools on Dev Shed!

#1
October 22nd, 2008, 02:33 PM
 Tubos
Registered User

Join Date: Apr 2006
Posts: 6
Time spent in forums: 2 h 53 m 50 sec
Reputation 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
October 22nd, 2008, 02:53 PM
 swampBoogie
Contributing User

Join Date: Jan 2003
Location: Paris Uppland
Posts: 2,361
Time spent in forums: 1 Month 4 Days 22 h 58 m
Reputation Power: 390

min:

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

max:

Code:
`case when a > b then a else b end`

#3
October 24th, 2008, 05:46 PM
 leitnin
Registered User

Join Date: Oct 2008
Posts: 1
Time spent in forums: 2 m 41 sec
Reputation 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
October 25th, 2008, 10:14 AM
 Tubos
Registered User

Join Date: Apr 2006
Posts: 6
Time spent in forums: 2 h 53 m 50 sec
Reputation Power: 0

Thank you!

#5
October 25th, 2008, 02:10 PM
 r937
SQL Consultant

Join Date: Feb 2003
Posts: 26,816
Time spent in forums: 3 Months 1 Week 4 Days 8 h 8 m 36 sec
Reputation Power: 4208
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

__________________
r937.com | rudy.ca

#6
October 25th, 2008, 02:48 PM
 swampBoogie
Contributing User

Join Date: Jan 2003
Location: Paris Uppland
Posts: 2,361
Time spent in forums: 1 Month 4 Days 22 h 58 m
Reputation Power: 390

Quote:
 Standard SQL functions LEAST() and GREATEST()

Nope, they are not standard.

#7
October 25th, 2008, 04:44 PM
 r937
SQL Consultant

Join Date: Feb 2003
Posts: 26,816
Time spent in forums: 3 Months 1 Week 4 Days 8 h 8 m 36 sec
Reputation Power: 4208
thanks, swampboogie, i thought they were, but you're right, they aren't

#8
December 30th, 2012, 02:40 PM
 jessecarllane
Registered User

Join Date: Dec 2012
Posts: 1
Time spent in forums: 18 m 56 sec
Reputation 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!

 Viewing: Dev Shed Forums > Databases > MS SQL Development > Select the smaller of two numbers