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