How to write a function that returns the sum of the numbers with biggest absolute values in T-SQL-CodePudding

In 2018, on a test about databases (which I have failed multiple times) at my university, they had this task:

Napisati T-SQL funkciju koja prima 3 realna broja, te kao rezultat vraća onaj broj koji ima najveću apsolutnu vrijednost. Ako slučajno istu najveću apsolutnu vrijednost imaju dva ili tri broja potrebno je vratiti zbroj tih brojeva.

That is:

Write a T-SQL function that accepts 3 real numbers, and as a result it returns the number that has the biggest absolute value. If accidentally the same biggest absolute value is got by two or three numbers it is necessary to return the sum of those numbers.

How would you solve that?

Here is my attempt, I do not know if it is correct nor how to actually test it on my computer:

CREATE FUNCTION max_abs3(@x, @y, @z DECIMAL) RETURNS DECIMAL
BEGIN
    IF ABS(@x)=ABS(@y) AND ABS(@y)=ABS(@z)
    BEGIN
        RETURN @x @y @z;
    END;
    IF ABS(@x)=ABS(@y) AND ABS(@x)>ABS(@z)
    BEGIN
        RETURN @x @y;
    END;
    IF ABS(@x)=ABS(@z) AND ABS(@x)>ABS(@y)
    BEGIN
        RETURN @x @z;
    END;
    IF ABS(@y)=ABS(@z) AND ABS(@y)>ABS(@x)
    BEGIN
        RETURN @y @z;
    END;
    IF ABS(@x)>ABS(@y) AND ABS(@x)>ABS(@z)
    BEGIN
        RETURN @x;
    END;
    IF ABS(@y)>ABS(@z) AND ABS(@y)>ABS(@x)
        RETURN @y;
    END;
    RETURN @z;
END;

I have only managed to install SQLite on my computer, and it apparently does not support custom SQL functions.

CodePudding user response：

Assuming that by "in 2018" you mean the solution must of worked in 2018 (so SQL Server 2017 or prior), then you could use a VALUES clause to make your values into a dataset, and then check to see if the value is the maximum value and then aggregate.

As you need to do this yourself, this here's what the logic inside the function would look like: I leave you to put this appropriately into a FUNCTION (ideally an inline table value function):

SELECT SUM(V.R)
FROM (VALUES(@x),(@y),(@z))V(R)
WHERE ABS(V.R) = (SELECT MAX(sq.R)
                  FROM (VALUES(@x),(@y),(@z))sq(R));

db<>fiddle

In SQL Server 2022 there is a new function, GREATEST which would look like this:

WITH V AS(
    SELECT R
    FROM (VALUES(@x),(@y),(@z))V(R))
SELECT SUM(R)
FROM V
WHERE ABS(V.R) = GREATEST(@x,@y,@z);