1

I have a lot of columns with numbers in them in a SQL Server table. I need to check the specific columns for the highest number and output the name of the column for each row in the table.

For example:

RED | BLUE | GREEN | BLACK | Highest Column

0      2       1       4         BLACK <-- Outputted result of an expression

I have a dataset that pulls all the columns from the database table. I need an expression that will evaluate the data and return the highest valued column name.

I'm not sure of the logic behind this type of situation. Any help would be appreciated.

This is an SSRS report.

Need to find the biggest value of all these columns for each row

Brett Ezra
  • 121
  • 1
  • 11
  • 2
    What if there is more than 1 column that holds the highest value? – NickyvV Jun 02 '15 at 14:44
  • It is an indication that your data model is somewhat broken when you want to deal with data in *multiple* columns in the same way. It's usually an indication that the columns should have been `Colour` and `Value` (or `Score`, or whatever each of these numbers represents) with 4 *rows* to store this data. That way, the colours end up as *data* rather then embedded in the table *metadata*, and the query is trivial to write as well. – Damien_The_Unbeliever Jun 02 '15 at 14:50
  • Also, when you want to correct something in your question, you can [edit] it, rather than having to write a comment below it. (For formatting, the `{}` button is your friend) – Damien_The_Unbeliever Jun 02 '15 at 14:54
  • The color and the value is arbitrary information. The actual data works fine. I have dumbed down the actual structure to make it easier to troubleshoot. – Brett Ezra Jun 02 '15 at 15:02
  • Even if you've abstracted the concept, the point still stands - data that you want to compute against (compute aggregates, compare values, etc), *ought* to be in a single column and you'll find that *data* has become embedded in column *names* where it can't be queried against. – Damien_The_Unbeliever Jun 02 '15 at 17:14
  • For NickyvV: If two columns are both the highest, then only the first catch is needed. – Brett Ezra Jun 02 '15 at 17:50

1 Answers1

3

Use CROSS APPLY. This will give you the highest valued color(black):

DECLARE @t table(red int, blue int, green int, black int)-- | Highest Column
INSERT @t values(0,2,1,4)

SELECT red, blue, green, black, highest 
FROM @t -- replace @t with your own table
CROSS APPLY
(SELECT top 1 color highest
FROM
  (VALUES('RED', red), ('BLUE', blue), 
  ('GREEN', green), ('BLACK', black)) x(color, value)
ORDER BY value desc) x

It should be quite easy to replace @t with your own table.

t-clausen.dk
  • 43,517
  • 12
  • 59
  • 92