sqlsql-serverstored-procedures

Self join to compare rows of table?


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

We have complex requirement to show only latest revision to user have access to.

We want to write a stored 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 we can implement above logic in a stored procedure.


Solution

  • 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