Self join to compare rows of table?

Question

We are using SQL Server 2017 for our document management application. In our application one document can have many versions. Access can be given to users at revision level. We store user access information in DOCUMENT_ACL table.

DocId	RevisionId	UserId
11	101	1001
11	101	1002
11	101	1003
11	102	1003
11	102	1004
11	103	1004
11	103	1005

UserId 1001 , 1002 & 1003 has access to RevisionId 101. UserId 1003 & 1004 has access to RevisionId 102. UserId 1004 & 1005 has access to RevisionId 103.

We have complex requirement to show only latest revision to user have access to. We want to write store procedure to return resultset as below.

e.g.

RevisionId	UserIds
101	1001,1002
102	1003
103	1004,1005

For RevisionId 103 (Version 3 - Latest) it should return both UserId 1004 & 1005.

For RevisionId 102 (Version 2) it should return only UserId 1003 (as 1004 user is already return for version 103).

For RevisionId 101 (Version 1) it should return only UserId 1001 & 1002 (as 1003 user is already return for version 102).

We looked at self join to achieve above but it look too complex . Please suggest how can implement above logic in store procedure?

Answer 1

Sounds like a simple group by?

with data as (
    select docid, rev
    , users
    from (
        values  (11, 101, 1001)
        ,   (11, 101, 1002)
        ,   (11, 101, 1003)
        ,   (11, 102, 1003)
        ,   (11, 102, 1004)
        ,   (11, 103, 1004)
        ,   (11, 103, 1005)
        ) x(docid,rev, users)
)
select docid, maxrev, string_agg(users,',') as users
from (
    select  docid, max(rev) as maxrev, users
    from    data
    group by docid, users
    ) x
group by docid, maxrev

Outputs:

docid	maxrev	users
11	101	1001,1002
11	102	1003
11	103	1004,1005