SQL View Select only last of self referencing chain

Question

I have a table that stores a string specifying user's permissions. I have a column previous that has a FK to id in the same table, and this way linking to the previous version of the permissions. What I want to do is create a view that will only show the last of this chain (the most up-to-date one). The original one does not have any previous value set. The table than looks like this:

+-------+-------+------+----------+---------+
| ID    | PERMS | USER | PREVIOUS | VERSION |
+-------+-------+------+----------+---------+
| GUID1 | set1  | John |          | 1       |
| GUID2 | set2  | Dave |          | 1       |
| GUID3 | set3  | John | GUID1    | 2       |
| GUID4 | set4  | Phil |          | 1       |
| GUID5 | set5  | John | GUID3    | 3       |
| GUID6 | set6  | Dave | GUID2    | 2       |

and so on...

What I want to get from the view is just John's set5, Dave's set6 and Phil's set4. Google search didn't bring anything useful, surprisingly...

Thanks in advance for any help!

score 0 · Answer 1 · answered Nov 11 '17 at 09:49

0

You can do that by version field

select * 
  from table as T
 where version = 
       ( select max(version)
           from table 
          where User = T.User )

answered Nov 11 '17 at 09:49

Sonikas

131
1
1
11

as @vianov mentioned CTE is not needed because in table is latest version number of permission – Sonikas Nov 11 '17 at 09:55

score 0 · Accepted Answer · answered Nov 11 '17 at 10:11

0

You can try this:

SELECT ID,PERMS,USERS,PREVIOUS,VERSION
          FROM 
          (select ID,PERMS,USERS,PREVIOUS,VERSION,
          ROW_NUMBER() OVER (PARTITION BY  USERS ORDER BY VERSION DESC) rn
          from Tab) A where rn=1

answered Nov 11 '17 at 10:11

Anagha

918
1
8
17

score 0 · Answer 3 · answered Nov 11 '17 at 11:53

This worked. It basically groups the permissions by the user to whom it belongs, numbers each row and then selects only those with rk=1.

WITH summary AS (
    SELECT p.id, 
        p.perms,
        p.user,
        p.previous,
        ROW_NUMBER() OVER(PARTITION BY p.user 
            ORDER BY p.version DESC) AS rk
FROM dbo.permissions p)
SELECT s.id, 
    s.user,
    s.perms,
    s.previous
    FROM summary s
    WHERE s.rk = 1

Code taken from https://stackoverflow.com/a/3800572/6241823

SQL View Select only last of self referencing chain

3 Answers3