Timeline for Max function in SQL Server that takes two values like Math.Max in .NET
Current License: CC BY-SA 3.0
22 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Aug 10, 2024 at 23:41 | comment | added | Jack Deeth | This works for numbers; it doesn't work for data which can be compared/ordered but not multiplied, such as strings. | |
| Jun 10, 2020 at 10:00 | comment | added | bastio84 | Definitely an elegant solution, which avoids comparison... Thanks! | |
| Mar 18, 2019 at 19:36 | comment | added | Mr.Z | Someone worked out the proof ♥ | |
| Feb 13, 2019 at 21:33 | comment | added | Ross Presser | If either value is null, you get null. Probably not what's wanted. | |
| Dec 11, 2017 at 9:49 | comment | added | xxyzzy | Downvoted because it uses cleverness where a clean solution is available. It also produces a number that is not guaranteed to be equal to either column, due to arithmetic round-off errors. CASE statement should be used (or the equivalent IIF function if available). Greenoldman said it right. | |
| Jul 27, 2016 at 11:45 | comment | added | Stefan Steiger | @futureSPQR: CAST(0.5 * ((CAST(@val1 as float)+ CAST(@val2 AS float)) + ABS(cast(@val1 as float)- CAST(@val2 AS float))) AS datetime) | |
| Dec 7, 2015 at 15:48 | history | bounty awarded | CommunityBot | ||
| Jul 9, 2015 at 20:14 | comment | added | Trisped | Loss of precision when using float types means that you might not get the actual max value, only a number which is really close. | |
| May 21, 2015 at 17:18 | comment | added | Michael Petito |
Since it wasn't immediately obvious, this works because the ABS expression computes the magnitude of the difference between the min and max value. Adding that back on to the sum of the min and max value makes the sum twice the max value (which is always divisible by 2). Dividing by 2 returns the max value. I believe you could change this to achieve the functionality of MIN by subtracting the ABS expression instead of adding.
|
|
| Jul 24, 2013 at 6:02 | history | edited | splattne | CC BY-SA 3.0 |
added 67 characters in body
|
| Jul 24, 2013 at 1:03 | comment | added | Adrian Torrie |
Old thread I know but this works for dates (it uses the formula above): dateadd(dd, ((datediff(dd, 0, [Date1]) + datediff(dd, 0, [Date2])) + abs(datediff(dd, 0, [Date1]) - datediff(dd, 0, [Date2]))) / 2, 0)
|
|
| Jan 24, 2012 at 15:24 | comment | added | Maxy-B | This will work for integers, etc., but not for other data types like datetime (which was my particular use case -- construct a column in the result set that at each row has the maximum (i.e., most recent) of two date columns). | |
| Jun 30, 2011 at 11:45 | comment | added | Johan |
@King, it not floatingpoint per say, if you replace the * 0.5 with a div 2, it's integer math again. This will also work very nice in other code, because you're avoiding a if-then jump and thus a branche mispredict (at the expense of a longer critical path obviously).
|
|
| May 18, 2011 at 15:43 | comment | added | KingErroneous | Neat mathematical trick, but I cringe at the idea of doing floating point arithmetic to get a max of two integers. | |
| Mar 18, 2011 at 4:32 | comment | added | Rob | I disagree with marcias. Code doesnt necessarily itself need to explicitly express the aim, as long as comments allow one to work it out. If you are doing any complex mathematical equations in code (or anywhere) its sometimes kind of hard to make it self descriptive. As long as its broken up into simpler, easier to understand parts then that is correct programming. | |
| Jan 11, 2011 at 12:16 | comment | added | splattne | It may be "dirty", but it could be the only option for databases with simple SQL dialects. | |
| Jan 11, 2011 at 9:50 | comment | added | greenoldman | This is extremely "dirty" "trick". When programming your code should explicitly express the aim, however in your case it looks like code taken from obfuscation contest. | |
| Aug 7, 2010 at 7:15 | history | edited | splattne | CC BY-SA 2.5 |
added 152 characters in body
|
| Oct 26, 2009 at 13:18 | comment | added | redcalx | I've never seen that before. Genius. | |
| Jun 10, 2009 at 13:02 | comment | added | AakashM | This way will give an overflow error if the sum is greater than can be stored in an int: declare @val1 int declare @val2 int set @val1 = 1500000000 set @val2 = 1500000000 SELECT 0.5 * ((@val1 + @val2) + ABS(@val1 - @val2)) -- => overflow error | |
| Jun 4, 2009 at 20:00 | comment | added | tom | +1 I believe you have provided the most correct way. "SELECT ((@val1+@val2) + ABS(@val1-@val2))/2 as MAX_OF_TWO" Also remember, "SELECT ((@val1+@val2) - ABS(@val1-@val2))/2 as MIN_OF_TWO". | |
| Nov 16, 2008 at 11:45 | history | answered | splattne | CC BY-SA 2.5 |