Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

MAX/MIN as NON-aggregate function

Please add the GREATEST/LEAST functions, just like in PostgreSQL/Oracle (with the same name for syntax-compatibility).
SQL-Server lacks functions for MAX/MIN as NON-aggregate function.
e.g. When I need the MAXIMUM of two values, or the minimum of two values (in columns, not rows).
When the value is a lengthy expression, it sucks having to write it in a case expression.
Also, the case-expression doesn't work for N > 2, and having to put it into a subselect-value-statement to use the aggregate functions as workaround affects readability severely (and thus maintainability).
Also, since SQL-server user-defined functions are damn slow, and on top of that don't allow a variable number of arguments, they are not a viable supplement either.

To me, adding these looks pretty easy (and low cost/complexity), and I simply don't understand why they weren't added a long time ago.

214 votes
Sign in
(thinking…)
Sign in with: oidc
Signed in as (Sign out)

We’ll send you updates on this idea

AdminMicrosoft SQL Server (Product Manager, Microsoft Azure) shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

Upvotes: 8

<=-=Mar 5 2017 2:42PM=-=>

Thanks for this idea. This is a valid requirement and I hope that it will get more votes. Currently we cannot confirm when it will be added, but it is in our backlog.

<=-=May 22 2017 5:03AM=-=>

would like it very much, particularly since you already have the CONCAT / GREATEST() a variable number of paramenters and does something with it…

<=-=Jun 5 2017 12:31PM=-=>

GREATEST / LEAST functions would be fantastic addition.

<=-=Nov 14 2017 3:42PM=-=>

The workarounds using CROSS APPLY or CASE expressions are difficult to manage and read. I’d love to see these implemented.

5 comments

Sign in
(thinking…)
Sign in with: oidc
Signed in as (Sign out)
Submitting...
  • Sandip Mehta commented  ·   ·  Flag as inappropriate

    These functions will significantly reduce code size and errors.

    Also, please consider adding aggregate function Product().

  • Anonymous commented  ·   ·  Flag as inappropriate

    Easiest way to work around this is to use a derived table e.g.:

    SELECT a.asset_id, a.added_on, a.changed_on
    ,(
    SELECT MAX(EditDate)
    FROM (VALUES(a.added_on), (a.changed_on)) AS bothDates(EditDate)
    ) as lastEdit
    FROM Asset a;

    Not as easy as a NON-aggregate column, but will work quite efficiently. It must be noted that in some cases CASE statements may be more performant. But until this feature is added to SQL Server it is quite a cool work around.

  • Petr Pařík commented  ·   ·  Flag as inappropriate

    Names are not so important, these do not have to necessarily be named MIN and MAX. But please implement functions with the described functionality - it will help to make code much more readable.

    Also, from my point of view, variable number of arguments is very important, but can be implemented later, if it speeds up the implementation.

  • ID commented  ·   ·  Flag as inappropriate

    IMAX and IMIN would be ok as well just like IIF

  • JediSQL commented  ·   ·  Flag as inappropriate

    Yes, I would like this with allowing an arbitrary number of arguments.

Feedback and Knowledge Base