SQL SELECT TOP 1 FOR EACH GROUP

Question

I have had a look through the other questions and can't quite find what i'm looking for I have an SQL Database and in it a table called InventoryAllocations. In the table I have multiple entries for DocumentID's and want to retrieve the last entry for each unique DocumentID. I can retrieve just one by doing

SELECT  top(1) [UID]
      ,[RecordStatusID]
      ,[CreatedDate]
      ,[CreatedTime]
      ,[CreatedByID]
      ,[OperationType]
      ,[InventoryLocationID]
      ,[DocumentTypeID]
      ,[DocumentID]
      ,[SOJPersonnelID]
      ,[InventorySerialisedItemID]
      ,[TransactionQty]
      ,[TransactionInventoryStatusID]
      ,[Completed]
      ,[CreatedByType]
      ,[RecordTimeStamp]
  FROM [CPData].[dbo].[InventoryAllocations]
  order by DocumentID desc

but I want it to bring back a list containing all the unique DocumentID's.I hope you can help. Many Thanks Hannah x

Answer 1

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).