SQL SELECT TOP 1 FOR EACH GROUP

SELECT TOP 1 WITH TIES 
    [UID]
    ,[RecordStatusID]
    ,[CreatedDate]
    ,[CreatedTime]
    ,[CreatedByID]
    ,[OperationType]
    ,[InventoryLocationID]
    ,[DocumentTypeID]
    ,[DocumentID]
    ,[SOJPersonnelID]
    ,[InventorySerialisedItemID]
    ,[TransactionQty]
    ,[TransactionInventoryStatusID]
    ,[Completed]
    ,[CreatedByType]
    ,[RecordTimeStamp]
FROM 
    [CPData].[dbo].[InventoryAllocations]
ORDER BY
    ROW_NUMBER() OVER(PARTITION BY DocumentID ORDER BY [RecordTimeStamp] DESC);

TOP 1 works with WITH TIES here.

WITH TIES means that when ORDER BY = 1, then SELECT takes this record (because of TOP 1) and all others that have ORDER BY = 1 (because of WITH TIES).


You can use a RowNumber() Window Function.

SELECT * FROM(
    SELECT 
            ROW_NUMBER() OVER(PARITION BY [DOCUMENTID] ORDER BY [RecordTimeStamp] DESC) AS RowNumber, 
          ,[RecordStatusID]
          ,[CreatedDate]
          ,[CreatedTime]
          ,[CreatedByID]
          ,[OperationType]
          ,[InventoryLocationID]
          ,[DocumentTypeID]
          ,[DocumentID]
          ,[SOJPersonnelID]
          ,[InventorySerialisedItemID]
          ,[TransactionQty]
          ,[TransactionInventoryStatusID]
          ,[Completed]
          ,[CreatedByType]
          ,[RecordTimeStamp]
      FROM [CPData].[dbo].[InventoryAllocations] ) as A 
WHERE RowNumber = 1