8

How to implement the scalar MAX in Sql server (like Math.Max). (In essense I want to implement something like Max(expression, 0), to make negative values replaced by 0.)

I've seen in other threads solutions with

  • creating a scalar function (how's that with performance?)
  • case when expression > 0 THEN expression ELSE 0) (then 'expression' is evaluated twice?)
  • complicated uses of Max(aggregate).

What's the best? Why does Sql Server not have something like this built in? Any complications I don't see?

Quassnoi
  • 398,504
  • 89
  • 603
  • 604
Rein
  • 2,357
  • 1
  • 16
  • 8
  • http://stackoverflow.com/questions/989415/t-sql-equivalent-of-excel-max-function-to-return-larger-of-two-numbers – Mauricio Scheffer Oct 08 '09 at 13:18
  • http://stackoverflow.com/questions/124417/is-there-a-max-function-in-sql-server-that-takes-two-values-like-math-max-in-net – JoeCool Feb 10 '11 at 19:28
  • In order to get this feature into the product please vote on the Microsoft Connect item: http://connect.microsoft.com/SQLServer/feedback/details/767183/t-sql-provide-scalar-min-and-scalar-max – usr Oct 22 '12 at 20:22
  • The feature request has been moved and can be voted on [here](https://feedback.azure.com/forums/908035-sql-server/suggestions/32902729-max-min-as-non-aggregate-function) – Kissaki Jun 18 '20 at 09:58

3 Answers3

5

In all other major systems this function is called GREATEST.

SQL Server seriously lacks it.

You can make a bunch of case statements, or use something like this:

SELECT  (
        SELECT  MAX(expression)
        FROM    (
                SELECT  expression
                UNION ALL
                SELECT  0
                ) q
        ) AS greatest
FROM    table_that_has_a_field_named_expression

The latter one is a trifle less performant than CASE statements.

Quassnoi
  • 398,504
  • 89
  • 603
  • 604
  • This is great because if the first expression is a complicated calculation and you're comparing it to a constant, you only have to write the complicated calculation once! – John Jun 28 '12 at 15:01
  • 3
    In order to get this feature into the product please vote on the Microsoft Connect item: http://connect.microsoft.com/SQLServer/feedback/details/767183/t-sql-provide-scalar-min-and-scalar-max – usr Oct 22 '12 at 20:22
  • Voted! This would definitely be a useful addition! – eidylon Feb 12 '13 at 16:53
  • Hi! Do you know if this has since been added to SQL Server? I checked the MS Connect link but looks like they closed down Connect. Thx. – sparc_spread Feb 07 '18 at 16:04
  • OK thanks, I checked some more as well, couldn't find anything. It's too bad because I think other RDBMS's have it. – sparc_spread Feb 08 '18 at 20:36
  • The feature request has been moved and can be voted on [here](https://feedback.azure.com/forums/908035-sql-server/suggestions/32902729-max-min-as-non-aggregate-function) – Kissaki Jun 18 '20 at 09:57
1

you want to create a user-defined scalar function named MAX in sql server to take two parameters as input, an expression and a min value, and return the expression if it exceeds the min value, otherwise return the min value?

I wouldn't worry about the performance of scalar functions, let the server do that. I'd also use IF instead of CASE to test for > min value.

SQL server doesn't have a built in function for you because they didn't think it would be widely enough used, I guess.

Beth
  • 9,425
  • 1
  • 21
  • 41
  • about the performance of scalar functions: they can be really painfull, but i think it is safe to assume that as long as there is no table/view logic involved, they should be quite fast (like built in system functions?) – Rein 0 secs ago – Rein Aug 20 '09 at 16:12
  • IF and CASE are different statement types. Whether you can use IF over CASE depends on where you want to use it. – Kissaki Jun 18 '20 at 09:45
0

The query optimizer should prevent the expression from being calculated multiple times.

For readability / maintainability, consider using a CTE to calculate the expression before the CASE statement.

richardtallent
  • 33,506
  • 13
  • 81
  • 119