sqlsql-servergreatest-n-per-group

SQL SELECT TOP 1 FOR EACH GROUP


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.


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