10

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
  • 413,100
  • 91
  • 616
  • 614
Rein
  • 2,487
  • 1
  • 17
  • 9
  • 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
  • 2
    Good news! SQL Server 2022 will finally implement [GREATEST](https://learn.microsoft.com/en-us/sql/t-sql/functions/logical-functions-greatest-transact-sql?view=azure-sqldw-latest). – Branko Dimitrijevic Aug 24 '22 at 06:59

4 Answers4

6

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
  • 413,100
  • 91
  • 616
  • 614
  • 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
  • Hello from the future and the past. As of Sql Server 2022 `GREATEST` is supported. – RubberDuck Jul 31 '23 at 12:42
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,531
  • 1
  • 24
  • 43
  • 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
  • 34,724
  • 14
  • 83
  • 123
0

As of Sql Server 2022, the GREATEST function is what you're looking for.

https://learn.microsoft.com/en-us/sql/t-sql/functions/logical-functions-greatest-transact-sql?view=azuresqldb-mi-current

SELECT GREATEST(expr1, expr2)
RubberDuck
  • 11,933
  • 4
  • 50
  • 95