SQL SELECT TOP 1 FOR EACH GROUP

greatest-n-per-group, sql, sql-server

Solution

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

Problem

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

Original source

Related problems