Selecting latest document from a table based on revision number

1

Hi,

I have a table which holds a document number, revision number and userid

SELECT DocumentID, RevNr, UserID FROM [CMD].dbo.Revisions

I am wanting to return the latest revision number for each document stored in the table. I have tried using DISTINCT and GROUP BY but as the RevNr and UserID may vary against each DocumentID I get several records for each DocumentID.

The closest I got was

SELECT DocumentID, MAX(RevNr) as 'Latest Revision' FROM dbo.Revisions GROUP BY DocumentID

But as soon as I tried adding in the UserID it didn't work. I have also tried different JOINS but I have had no success, so any help would be great.

2

Use the ROW_NUMBER function like shown below:

;WITH cte AS
(
	SELECT DocumentID, RevNr, UserID,
		ROW_NUMBER() OVER (PARTITION BY DocumentId ORDER BY RevNr DESC) AS N
	FROM [CMD].dbo.Revisions
)
SELECT DocumentID, RevNr, UserID
from cte
WHERE N = 1;
1 Like
3

That works a treat!