Finding columns with highest value among other columns in SQL

Question

I have a table that looks like this:

table,
th,
td {
  border: 1px solid black;

<table>
  <tr>
    <th>Customer</th>
    <th>Category 1</th>
    <th>Category 2</th>
    <th>Category 3</th>
    <th>Category 4</th>
  </tr>
  <tr>
    <td>aaaaa@aaa.com</td>
    <td>0</td>
    <td>563</td>
    <td>0</td>
    <td>0</td>
  </tr>
  <tr>
    <td>bbbb@bbb.com</td>
    <td>33</td>
    <td>31</td>
    <td>38</td>
    <td>13</td>
  </tr>
  <tr>
    <td>cccc@ccc.com</td>
    <td>108</td>
    <td>0</td>
    <td>0</td>
    <td>0</td>
  </tr>
  <tr>
    <td>dddd@ddd.com</td>
    <td>0</td>
    <td>7</td>
    <td>0</td>
    <td>11</td>
  </tr>
</table>

I am trying to insert a new column named "BestCategory" that will show the name of the category that has the highest value in between them.

I have tried to use GREATEST but it's not accepted in my system.

Can you guys help me?

possible duplicate of [SQL MAX of multiple columns?](http://stackoverflow.com/questions/71022/sql-max-of-multiple-columns) — AHiggins, Sep 23 '15 at 15:41
You may want to edit your post to show a non-html table, and possibly data types. — ergonaut, Sep 23 '15 at 15:49

score 1 · Accepted Answer · answered Sep 23 '15 at 17:27

First you have to use UNPIVOT to calculate the maxValue for each row

Then use a CASE to select what is the BestCategory.

Sql Fiddle Demo

WITH maxValues as 
(
   select
     [Customer], Max(Amount) as TheMax
   from 
     Customer 
     UNPIVOT (Amount for AmountCol in 
              ([Category 1], [Category 2], [Category 3], [Category 4])) as unpvt
   group by [Customer]
)        
select 
    Customer.[Customer], [Category 1], [Category 2], [Category 3], [Category 4], 
    TheMax,
    Case 
        WHEN [Category 1] = TheMax THEN '[Category 1]'
        WHEN [Category 2] = TheMax THEN '[Category 2]'
        WHEN [Category 3] = TheMax THEN '[Category 3]'
        ELSE '[Category 4]'
    END  as BestCategory        
from Customer        
inner join maxValues
   on Customer.[Customer] = maxValues.[Customer]

OUTPUT

|      Customer | Category 1 | Category 2 | Category 3 | Category 4 | TheMax | BestCategory |
|---------------|------------|------------|------------|------------|--------|--------------|
| aaaaa@aaa.com |          0 |        563 |          0 |          0 |    563 | [Category 2] |
|  bbbb@bbb.com |         33 |         31 |         38 |         13 |     38 | [Category 3] |
|  cccc@ccc.com |        108 |          0 |          0 |          0 |    108 | [Category 1] |
|  dddd@ddd.com |          0 |          7 |          0 |         11 |     11 | [Category 4] |

score 0 · Answer 2 · edited Sep 23 '15 at 20:18

If you want the columnname in the "greatest field" you could use the idea in this sqlfiddle example

It selects the columnname with the greatest value per row

SELECT CASE 
        WHEN [category 1] > [category 2]
            AND [category 1] > [category 3]
            AND [category 1] > [category 4]
            THEN '[Category 1]'
        ELSE CASE 
                WHEN [category 2] > [category 1]
                    AND [category 2] > [category 3]
                    AND [category 2] > [category 4]
                    THEN '[Category 2]'
                ELSE CASE 
                        WHEN [category 3] > [category 1]
                            AND [category 3] > [category 2]
                            AND [category 3] > [category 4]
                            THEN '[Category 3]'
                        ELSE '[category 4]'
                        END
                END
        END
FROM customer

Finding columns with highest value among other columns in SQL

2 Answers2