title: Select distinct on foreign key, inner join another table
body: <p>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.</p>
  
  <p>The only thing I want from permission is RelationId.</p>
  
  <p>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.</p>
  
  <p>What I've came up with so far:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>It need to use OFFSET X ROWS and FETCH NEXT 3 ROWS ONLY for paging</p>
  
  <p>This doesn't work for some reason, because I get duplicates on RelationId.
  No errors.</p>
  
  <p>example data: </p>
  
  <pre><code>action (
      id            INTEGER       PRIMARY KEY,
      ActionType    VARCHAR(50)   not null,
      RelationId    INTEGER       ForeignKey,
      Deleted       Bit           not null,
      TimeStamp     DATE          not null,
      UserId        INTEGER       ForeignKey
  );
  </code></pre>
  
  <p>Desired result: 3 last actions made by one user that has permissions for it distinct by RelationId.</p>