0

I am fairly new to T-SQL and advanced concepts/queries and I have difficulty trying to figure out the situation described further. Lets say, I have a table like this:

--------------------
| K | C1 | C2 | C3 |
--------------------
| 1 | 14 | 24 | 48 |
| 2 | 10 | 65 | 37 | 
| 3 | 44 | 90 | 22 |
--------------------

I need to find maximums row-wise, which has already been asked many times on SO, AND what makes difference, in addition, I also want the columns' names where found maximums occurred. The result has to be like this:

-- how to get Col column?
-----------------
| K | Max | Col |
| 1 | 48  | C3  |
| 2 | 65  | C2  | 
| 3 | 90  | C2  |
-----------------

I learned how to find maximum rows-wise based on this and this threads, but I cannot understand how I could add another column containing columns' names where max. values occurred. I know I kinda have to show efforts but actually I cannot show anything since I just don't quite understand how to do it and all my attempts just return errors. Any ideas, hints? Thanks in advance!

curveball
  • 4,320
  • 15
  • 39
  • 49

2 Answers2

1

The simplest way in SQL Server is to use apply:

select t.k, v.col, v.val
from t cross apply
     (select top 1 v.col, v.val
      from (values ('C1', t.C1), ('C2', t.C2), ('C3', t.C3)
           ) v(col, val)
      order by val desc
     ) v(col, val)
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • first of all, thanks for your solution! It is working indeed! But what if there are 2 rows in the original table with maximum values? For instance, lets say, what if`1 | 48 | 24 | 48` was in the original table? Is there a way to include both C1 and C3 rows in the result table? – curveball Nov 25 '17 at 16:42
  • In order to get multiple max. values records in the result table, I could only come up with getting rid of 'top 1' and order-by clause and then doing a correlated subquery to the same table like `...from table t1 where not exists(...from table t2 where t2.val > t1.val)` where t1 and t2 are the same table referred twice. Anyway, your solution worked fine for what I have asked for! Thank you again! – curveball Nov 25 '17 at 17:19
  • Im not sure if this would work for you, but possibly a group_concat for that occurence – Evan Callahan Nov 26 '17 at 01:58
  • 2
    @curveball . . . The subquery would be `select top (1) with ties`. – Gordon Linoff Nov 26 '17 at 03:23
1

I would approach this problem by joining a temporary maximum table back to the original and then writing a long "case when", assuming you have a reasonable number of columns. It would look like the following.

Select a.K
, b.Max_Value
, Case When a.Column_1 = b.Max_Value Then C1
When a.Column_2 = b.Max_Value Then C2
When a.Column_3 = b.Max_Value Then C3
... As Col
From Your_Table as a Left Join Max_Table as b on a.K = b.K

I believe this should get you what you want if I understood the question correctly. If you have a lot of columns where this would get too long then there's probably a better approach.

Evan Callahan
  • 79
  • 6
  • thank you for your participation and idea! Yes, you are right - there are quite a few columns and case-clause would be too large. – curveball Nov 25 '17 at 16:48