Is kind of frustrating when you are trying to write a complex query and there is that one function not provided by SQL Server out of the box to use it right away. This is the case with a function to compare 2 integers to get the Max or Min between them, similar to the Math.Max and Math.Min methods in .NET. You could create your own function and re-use it everywhere, but the first implementation that comes to your mind is a very inconvenient way to deal with this, since you cannot use it (easily) within an inline Sql query. Let's take a look at the easiest way to implement our function:

[sourcecode language="sql"] create function dbo.MaxInt(@int1 int, @int2 int) returns int as begin if @int1 > @int2 return @int1 return @int2 end [/sourcecode]

As I mentioned, there is no easy way to have this query as a subquery in a complex sql statement. And if you make it possible, the performance implications can be kinda bad. But the world doesn't end here! There is a convenient solution that will allow you to write an efficient function for it, or even better, have it inline right within your query. The trick is to double the desired value and then divide it by 2. So if you want to find the max value, find the value that is 2 times the max and then divide it by 2; if you want to find the min value, find the value that is 2 times the min and then divide it by 2. Let's take a look:

Max Function

[sourcecode language="sql"] create function dbo.MaxInt(@int1 int, @int2 int) returns int as begin return ((@int1 + @int2) + ABS(@int1 - @int2))/2 end [/sourcecode]

Min Function

[sourcecode language="sql"] create function dbo.MinInt(@int1 int, @int2 int) returns int as begin return ((@int1 + @int2) - ABS(@int1 - @int2))/2 end [/sourcecode]

 

And using this doubling notion with the absolute value tricks, we can elegantly write min and max comparisons right inside the Sql statement without the need of a function. Here is an example of an inline Sql statement:

[sourcecode language="sql"] select a, b, ((a + b) + ABS(a - b))/2 as maxValue from myTable [/sourcecode]

Sweet ahh?! Happy coding!

2 Comments