Get the earliest record in this group with all the details

enter image description here

I need only the latest record with respect to each REV.NO. There are 2 revisions for REV.NO 2, I need the 26-Feb entry alone. I have to do this to get 100,000 records. Please help.

WITH DocumentAttribute AS ( SELECT * FROM (
       SELECT 
              [ParentDocumentId]
              ,[AttributeName]
              ,[AttributeValue]
              ,DocumentAttribute.[RowValidTo]
       FROM [ODS].[asite].[DocumentAttribute] FOR SYSTEM_TIME ALL DocumentAttribute
       LEFT JOIN [asite].[Document] FOR SYSTEM_TIME ALL document
              ON document.DocumentId = ParentDocumentId
              WHERE document.WorkspaceId IN ('1105994')  ) AS source_table PIVOT  (
       MAX([AttributeValue]) For [AttributeName] in
       (
       [Date Document Due],[Date Document Received],[Document Discipline],[Document Type],[Fabrication Package],[Fabrication
Recipient],
       [IFF Status],[Incoming Transmittal No.],[Model No.],[Model Revision],[Transmittal No.]
       ) ) AS PivotTABLE ) SELECT 
       [DocumentId],
       [DocTitle],
       [DocRef],
       [IssNo],
       [RevNo],
       LEFT([PublishedDate],11) AS PublishedDate,
       [PurposeOfIssue],
       [DocStatus],
       [Date Document Due],
       [Date Document Received] FROM [ODS].[asite].[Document] FOR SYSTEM_TIME ALL Document LEFT JOIN DocumentAttribute
       ON Document.DocumentId = DocumentAttribute.[ParentDocumentId]
       AND CAST(Document.RowValidTo AS DATE) = CAST(DocumentAttribute.RowValidTo AS DATE) WHERE WorkspaceId IN
('1105994') and DocRef = 'TL601-06MP005' --'KD-CH0202-001-24-1045'

Answer

I hope I understand what you means. Also, it is a good way to insert to a temp table.

SELECT ceq.documentId, 
       ceq.RevNo, 
       ceq.PublishedDate
FROM
    (
        SELECT c.documentId, 
               c.RevNo, 
               c.PublishedDate, 
               ROW_NUMBER() OVER(PARTITION BY c.RevNo
               ORDER BY c.PublishedDate) AS rn
        FROM tableC AS c
     ) AS ceq
WHERE ceq.rn = 1;