SQL Server Comparison in same row

Question

I use Microsoft SQL Server 2014. I want to get max number in same row.

For example, this table is here

 ------------------------------------------------------------------------
| Values_A |  Amounts_A  | Values_B |  Amounts_B  | Values_C |  Amounts_C| 
 ------------------------------------------------------------------------
|  5000    |      50     |   3000   |     100     |   1000   |   200     | 
 ------------------------------------------------------------------------

I want to get maximum Values/Amounts in A or B or C. In this case, A's Values/Amounts is 5000/50 = 100, B's Values/Amounts is 3000/100 = 30, C's Values/Amounts is 1000/200 = 5, So I want to get 100.

please tell me.

score 2 · Accepted Answer · answered Mar 13 '17 at 07:54

You can do this concisely using values in a subquery:

select 
    (select
        max(val)
        from (values (Values_A/Amounts_A),
                     (Values_B/Amounts_B),
                     (Values_C/Amounts_C)
        ) t (val)
    ) max_val
from your_table;

Tim Biegeleisen · Answer 2 · 2017-03-13T08:05:17.190

SQL Server's MAX() function was designed to return the greatest number in a column from a collection of records, not columns. I think the biggest challenge in your problem is that you have your data spread out across columns. One approach is to unpivot this data using UNION, and then just select the maximum ratio.

WITH cte AS (
    SELECT Values_A / Amounts_A AS ratio
    FROM yourTable
    UNION ALL
    SELECT Values_B / Amounts_B
    FROM yourTable
    UNION ALL
    SELECT Values_C / Amounts_C
    FROM yourTable
)
SELECT MAX(ratio)
FROM cte

This approach would work fine for a single record. If you have multiple records in your table, then a more ugly approach is this:

WITH cte AS (
    SELECT Values_A / Amounts_A AS A_ratio,
           Values_B / Amounts_B AS B_ratio,
           Values_C / Amounts_C AS C_ratio
    FROM yourTable
)
SELECT CASE WHEN t.A_ratio > t.B_ratio
            THEN CASE WHEN t.A_ratio > t.C_ratio THEN t.A_ratio ELSE t.C_ratio END
            ELSE CASE WHEN t.B_ratio > t.C_ratio THEN t.B_ratio ELSE t.C_ratio END
       END AS max_ratio
FROM cte t

SQL Server Comparison in same row

2 Answers2