Lastest Group Document belongs to

Question

I have two tables, doc_group and group (also document-table, but that is irrelevant here) and I want to get the latest Group for each Doc ID, based on Created date of the group.

DocID	GroupID
1	1
1	2
2	1
2	3

GroupID	Name	Created
1	Group 1	1.1.2022
2	Group 2	10.10.2022
3	Group 3	2.2.2022

I have tried different approaches, but I cannot get it return the just a single line per DocID.

Here is my latest approach:

SELECT 
    doc_group.DocID
    ,doc_group.GroupID
    , groups.Created
FROM 
    doc_group
JOIN 
    groups 
        ON groups.ID = (
            SELECT 
                TOP 1 ID
            FROM 
                groups
            WHERE 
                groups.ID = doc_group.GroupID
            ORDER BY Created DESC           
        )

Answer 1

Join and use the row_number function as the following:

SELECT DocID, GroupID, Created
FROM
(
  SELECT dg.DocID ,dg.GroupID , g.Created,
    ROW_NUMBER() OVER (PARTITION BY dg.DocID ORDER BY g.Created DESC) rn
  FROM doc_group dg JOIN groups g
  ON dg.GroupID = g.GroupID
) T
WHERE rn = 1
ORDER BY DocID

See demo