For rows with duplicate keys, how do I get longest record?

Question

Given the following table, lacking any unique key, how can I group by id and get the Permissions column with the longest length?

SampleTable:

id | Permissions
------------------------------------
1  | Walk, Swim
1  | Walk, Sit, Swim, Run, Jump, Lay
1  | !Walk, Sit, Lay
2  | Walk, Sit, Swim
3  | !Walk, Sit, Swim
3  | Walk, Sit, Swim

I tried:

SELECT r.id, r.Permissions
FROM SampleTable AS r
CROSS APPLY (
    SELECT TOP 1 u.id, u.Permissions
    FROM SampleTable AS u
    GROUP BY u.id, u.Permissions
    HAVING u.id = r.id
    ORDER BY MAX(LEN(Permissions)) DESC
) AS u

However I didn't get the correct results.

I'm looking for results like:

id | Permissions
-----------------------------------
1 | Walk, Sit, Swim, Run, Jump, Lay
2 | Walk, Sit, Swim
3 | !Walk, Sit, Swim

Edit:
This has already been answered, thanks. But as an aside, I should have had my SQL as:

SELECT r.id, u.Permissions
FROM SampleTable AS r
CROSS APPLY (
    SELECT TOP 1 u.id, u.Permissions
    FROM SampleTable AS u
    WHERE u.id = r.id
    ORDER BY LEN(Permissions) DESC
) AS u
GROUP BY r.id, u.Permissions

Possible duplicate of [Select first row in each GROUP BY group?](https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group) — tymtam, Oct 24 '19 at 04:20
You _really_ should move the `Permissions` to a table that you `JOIN` to your `SampleTable` so that you don't have to parse all the string information. That's the whole point of RDBMS. — daShier, Oct 24 '19 at 04:21
Take a second to think WHY this table does not have unique keys. Sometime (soon) you'll want to select one specific row - how are you going to pick it? If you haven't heard of Third-Normal-Form before, take some time to read about it because your one table is contrary to 3NF in multiple ways ! https://stackoverflow.com/questions/15126588/understanding-3nf-plain-english-please — racraman, Oct 24 '19 at 04:27
I didn't design the tables and I personally place auto-increment keys even on many-to-many relational tables. — Dark Snowy, Oct 24 '19 at 04:44

arcee123 · Accepted Answer · 2019-10-24T04:30:40.180

5

you can use row_number() over(). such like:

with cte as (
    Select id, Permissions
    row_number() over( partition by id order by LEN(Permissions) desc) as rnum
    from SampleTable
) Select id, Permissions from cte where rnum = 1

edited Oct 24 '19 at 04:30

answered Oct 24 '19 at 04:27

arcee123

101
9
41
118

This would 'drop' duplicates `(3, '!Walk, Sit, Swim');` `(3, '!Walk, Run, Swim');` – tymtam Oct 24 '19 at 04:58
I agree, however that's what is his design entails. his expected output is what I went with. One Permissions list per id. What I don't know is how to determine who came last or who is right in the list of duplicates? like latest setting. I am more inclined to agree with the commenters up top saying the design needs looking at than I am to recommend either of our solutions anyways. Yours is better quality SQL irregardless. – arcee123 Oct 24 '19 at 11:54

tymtam · Answer 2 · 2019-10-24T05:07:22.887

This literally is a 'who has the longest?' contest!

You could try this the following which is more fair then partitioning since it will return all candidates with max length.

SELECT p2.id, p2.permissions
FROM table1 p2
JOIN (SELECT p.id, MAX(LEN(p.permissions)) AS longest FROM table1 p GROUP BY p.id) 
p1 ON p1.id = p2.id
AND p1.longest = Len(p2.permissions)

Test

--drop table if exists table1
create table table1 ( id int, permissions nvarchar(100));

insert into table1 values(1, 'Walk, Swim');
insert into table1 values(1, 'Walk, Sit, Swim, Run, Jump, Lay');
insert into table1 values(1, '!Walk, Sit, Lay');
insert into table1 values(2, 'Walk, Sit, Swim');
insert into table1 values(3, '!Walk, Sit, Swim');
insert into table1 values(3, 'Walk, Sit, Swim');

SELECT p2.id, p2.permissions
FROM table1 p2
JOIN (SELECT p.id, MAX(LEN(p.permissions)) AS longest FROM table1 p GROUP BY p.id) 
p1 ON p1.id = p2.id
AND p1.longest = Len(p2.permissions)

Output

id  permissions
1   Walk, Sit, Swim, Run, Jump, Lay
2   Walk, Sit, Swim
3   !Walk, Sit, Swim

Multiple 'longests'

(...)
insert into table1 values(1, 'Walk, Swim');
insert into table1 values(1, 'Walk, Sit, Swim, Run, Jump, Lay');
insert into table1 values(1, '!Walk, Sit, Lay');
insert into table1 values(2, 'Walk, Sit, Swim');
insert into table1 values(3, '!Walk, Sit, Swim');
insert into table1 values(3, 'Walk, Sit, Swim');
insert into table1 values(3, '!Walk, Run, Swim'); -- extra record

Output:

id  permissions
1   Walk, Sit, Swim, Run, Jump, Lay
2   Walk, Sit, Swim
3   !Walk, Sit, Swim
3   !Walk, Run, Swim

There's a lot of scenarios where this information is useful. In my case, I'm trying to trim bad data down to a unique id. Then fix any incorrect permissions later. — Dark Snowy, Oct 24 '19 at 05:39

For rows with duplicate keys, how do I get longest record?

2 Answers2

Test

Output

Multiple 'longests'