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!

Improve this question

2 Answers 2

Reset to default
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)
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

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 if1 | 48 | 24 | 48 was in the original table? Is there a way to include both C1 and C3 rows in the result table?
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!
Im not sure if this would work for you, but possibly a group_concat for that occurence
@curveball . . . The subquery would be select top (1) with ties.
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.

Improve this answer

1 Comment

thank you for your participation and idea! Yes, you are right - there are quite a few columns and case-clause would be too large.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.