Grouping in T-SQL and choosing the longest name

Question

I am using SQL Server 2012. I was trying to resolve a "typical" grouping problem. But I couldn't resolve this correctly.

Imagine that you have this table:

I would like to group by position. It could be easy:

select position, COUNT(*)
from testsql
group by position

But, I would like to group by position, and take the longest name (myname) of every position.

select 
    myid, myname, position, len(myname) as lenname
from 
    testsql
group by 
    myid, myname, position
having
    myname = select (max(len(myname))) from testsql /*Obviously it doesn't work */
order by
    position asc;

It's a **position** - not a "possition" (one `s` is **quite** enough!) — marc_s, Feb 07 '18 at 19:28

score 3 · Answer 1 · answered Feb 07 '18 at 17:46

3

Here is another way of doing this using ROW_NUMBER. It only hits the base table one time.

select myid
    , myname
    , possition
from
(
    select myid
        , myname
        , possition
        , RowNum = ROW_NUMBER() over (partition by possition order by LEN(myname) desc)
    from testsql
) x
where x.RowNum = 1

answered Feb 07 '18 at 17:46

Sean Lange

33,028
3
25
40

2

If the implication is that the other answer will hit the table multiple times this may well not be the case. http://sqlblog.com/blogs/paul_white/archive/2010/07/28/the-segment-top-query-optimisation.aspx – Martin Smith Feb 07 '18 at 17:55
@SeanLange Totally correct. Your way is more efficient. Thanks for comment. – jorge_vicente Feb 07 '18 at 19:03

score 1 · Answer 2 · answered Feb 07 '18 at 17:42

I had thought to use a subquery with an alias. And finally, it would be the best option; because, after that, I make a join to link the MAX(LEN(myname) of the subquery with the myname of the normal table.

This is the solution. I am sure that maybe it is not the best.

select myid, myname, testsql.possition, LEN(myname) AS lenname
from testsql
    INNER JOIN (
        select possition, max(LEN(myname)) AS lenname2
        FROM testsql
        group by possition) as testsql2
            ON testsql.possition = testsql2.possition
where LEN(testsql.myname) = lenname2
GROUP BY myid, myname
ORDER BY possition ASC;

Please, don't doubt to post more comments if you want.

Thanks!

Grouping in T-SQL and choosing the longest name

2 Answers2