Writing a Function that Returns the Sum of Numbers with Biggest Absolute Values in T-SQL

Introduction to the Problem

In 2018, a student at a university was presented with a task related to databases. The task involved writing a T-SQL function that accepts three real numbers and returns the number with the biggest absolute value. If two or more numbers have the same maximum absolute value, the function should return the sum of those numbers.

Understanding the Requirements

To solve this problem, we need to understand the concept of absolute values in mathematics. The absolute value of a number is its distance from zero on the number line, without considering direction. For example, the absolute value of -3 is 3.

In T-SQL, we can use the ABS function to calculate the absolute value of a number.

The Challenge with Conditional Logic

The original solution attempts to solve this problem using conditional logic in T-SQL. However, as shown in the example, this approach has several issues:

It uses multiple IF-THEN statements, which can become complex and difficult to read.
It checks for every possible combination of absolute values, resulting in inefficient code.

A Better Approach: Using Aggregation

A more efficient approach is to use aggregation techniques. We can create a temporary table or view that contains the three input numbers and their absolute values. Then, we can use the MAX function to find the maximum absolute value and aggregate it using the SUM function.

Solution for SQL Server 2017 or Prior

For SQL Server versions prior to 2017, we can use a VALUES clause to create an inline table-valued function that returns the sum of numbers with the biggest absolute values. Here is an example:

CREATE FUNCTION max_abs3(@x, @y, @z DECIMAL) RETURNS DECIMAL
AS
BEGIN
    WITH V AS (
        SELECT R = ABS(@x), sq.R = ABS(@y), sqsq.R = ABS(@z)
        FROM (VALUES (@x), (@y), (@z)) sq(R)
    )
    SELECT SUM(V.R)
    FROM V
    WHERE V.R = (SELECT MAX(sq.R) FROM V sq);
END;

Solution for SQL Server 2022

For SQL Server versions 2022 or later, we can use the new GREATEST function to simplify the solution:

CREATE FUNCTION max_abs3(@x, @y, @z DECIMAL) RETURNS DECIMAL
AS
BEGIN
    WITH V AS (
        SELECT R = ABS(@x), sq.R = ABS(@y), sqsq.R = ABS(@z)
        FROM (VALUES (@x), (@y), (@z)) sq(R)
    )
    RETURN SUM(V.R)
    FROM V
    WHERE ABS(V.R) = GREATEST(@x, @y, @z);
END;

Conclusion

In this article, we explored how to write a function that returns the sum of numbers with biggest absolute values in T-SQL. We discussed the challenges with conditional logic and showed two efficient solutions using aggregation techniques. The code examples provided demonstrate how to use inline table-valued functions and the new GREATEST function to solve this problem.

Step-by-Step Solution

To write a similar function, follow these steps:

Define the function signature: Create a new T-SQL function that accepts three real numbers as parameters.
Use aggregation techniques: Instead of using conditional logic, create an inline table-valued function or view that contains the input numbers and their absolute values.
Find the maximum absolute value: Use the MAX function to find the maximum absolute value from the aggregated data.
Aggregate the result: Use the SUM function to aggregate the sum of numbers with the biggest absolute values.

By following these steps, you can write an efficient and readable T-SQL function that solves this problem.

Last modified on 2025-04-15