Tsql group get the alphanumeric value of the column with the maximum length

Question

I have a sql view, call it SampleView , the results of which have the following format.

 Id (INT), NameA (VARVHAR(50)), NameB (VARCHAR(50)), ValueA (INT), ValueB (INT) 

The result set of the view contains strings that can have the same Id or not. When there are two or more lines with the same identifier, I would like to get something like the following

 SELECT Id, MAX(NameA), MAX(NameB), MAX(ValueA), MAX(ValueB) FROM SampleView GROUP BY Id ORDER BY Id 

As for the columns Id , ValueA and ValueB , then no problems arise. On the other hand, using MAX for NameA and NameB , things are not going as expected. After some searching and searching, I realized that MAX does not have the “expected” behavior for alphanumeric columns. Speaking of the expected, I mean using MAX in my case, this would have to return the NameA value with the maximum number of characters, MAX(LEN(NameA)) . I should mention here that it is not possible for NameA have two values ​​for the same Id with the same length. This can make the task easier.

I am using SQL Server 2012 and TSQL .

Do you have any suggestion on how I can deal with this problem?

Thanks in advance for any help.

Answer 1

You can use window functions:

 SELECT DISTINCT id, FIRST_VALUE(NameA) OVER (PARTITION BY id ORDER BY len(NameA) DESC) AS MaxNameA, MAX(ValueA) OVER (PARTITION BY id) AS MaxValueA, FIRST_VALUE(NameB) OVER (PARTITION BY id ORDER BY len(NameB) DESC) AS MaxNameB, MAX(ValueB) OVER (PARTITION BY id) AS MaxValueB FROM SampleView 

Demo here

Answer 2

You can use correlated queries as follows:

 SELECT t.Id, (SELECT TOP 1 s.NameA FROM SampleView s WHERE s.id = t.id ORDER BY length(s.NameA) DESC) as NameA, (SELECT TOP 1 s.NameB FROM SampleView s WHERE s.id = t.id ORDER BY length(s.NameB) DESC) as NameB, MAX(t.ValueA), MAX(t.ValueB) FROM SampleView t GROUP BY t.Id ORDER BY t.Id 

Answer 3

One option is to use ROW_NUMBER twice:

 WITH CTE_NameA AS ( SELECT Id, NameA, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY LEN(NameA) DESC) AS rnA FROM SampleView ) ,CTE_NameB AS ( SELECT Id, NameB, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY LEN(NameB) DESC) AS rnB FROM SampleView ) SELECT Id, CTE_NameA.NameA, CTE_NameB.NameB, MAX(ValueA), MAX(ValueB) FROM SampleView INNER JOIN CTE_NameA ON CTE_NameA.Id = SampleView.Id AND CTE_NameA.rnA = 1 INNER JOIN CTE_NameB ON CTE_NameB.Id = SampleView.Id AND CTE_NameB.rnB = 1 GROUP BY Id ORDER BY Id;