Select distinct on foreign key, inner join another table

Question

Basically what I want to do is, select the 3 last actions made from the user. But no duplicates on RelationId, and also innerjoin permissions just to make sure the user has permission still to make the same action.

The only thing I want from permission is RelationId.

By no duplicated I mean if there is two rows of Action with the same RelationId the one closes to the top ( Ordered by TimeStamp ) should be picked.

What I've came up with so far:

SELECT DISTINCT a.*, p.RelationId
FROM [Action] [a]
INNER JOIN [Permission] p 
  ON ([p].[RelationId] = [a].[RelationId] 
  AND [p].[RelationType] = [a].[RelationType] 
  AND [p].[UserId] = [a].[UserId] 
  AND [p].[Deleted] = 0)
WHERE [a].[ActionType] = 'Clicked' 
AND [a].[RelationType] = 'Direct' 
AND [a].[UserId] = 5 
AND [a].[Deleted] = 0
ORDER BY [a].[TimeStamp] DESC
OFFSET 0 ROWS
FETCH NEXT 3 ROWS ONLY

It need to use OFFSET X ROWS and FETCH NEXT 3 ROWS ONLY for paging

This doesn't work for some reason, because I get duplicates on RelationId. No errors.

example data:

action (
    id            INTEGER       PRIMARY KEY,
    ActionType    VARCHAR(50)   not null,
    RelationId    INTEGER       ForeignKey,
    Deleted       Bit           not null,
    TimeStamp     DATE          not null,
    UserId        INTEGER       ForeignKey
);

Desired result: 3 last actions made by one user that has permissions for it distinct by RelationId.

Answer 1

Solved it by doing this. Don't know if its the best solution though.

SELECT [a].*, [p].[Id]
FROM (SELECT *, ROW_NUMBER() OVER(PARTITION BY [RelationId] ORDER BY [TimeStamp] DESC) AS row from [Action]) a
INNER JOIN [Permission] p ON ([ep].[RelationId] = [a].[RelationId] AND [p].[RelationType] = [a].[RelationType] AND [p].[UserId] = [a].[UserId] AND [p].[Deleted] = 0)
WHERE row = 1 AND [a].[ActionType] = 'Clicked' AND [a].[RelationType] = 'Direct' AND [a].[UserId] = 5 AND [a].[Deleted] = 0
ORDER BY [a].[TimeStamp] DESC
OFFSET 0 ROWS
FETCH NEXT 3 ROWS ONLY