tsql group by get alphanumeric column value with maximum length

Question

I have a sql view, let's call it SampleView, whose results have the following format.

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

The result set of the view contains rows that may have the same Id or not. When there are two or more rows with the same Id, 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

Regarding the columns Id, ValueA and ValueB there isn't any problem. On the other hand using MAX for both NameA and NameB things are not as expected. After some googling and searching I realized that MAX has not the "expected" behavior for alphanumeric columns. Saying the expected, I mean using MAX in my case, it would be to return the value of NameA with the maximum number of characters, MAX(LEN(NameA)). I have to mention here that there ins't any possibility for NameA to have two values for the same Id with the same length. This might makes the problem more easy to be solved.

I use SQL Server 2012 and TSQL.

Have you any suggestion on how I could deal with this problem?

Thank you very much 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 like this:

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