Menu
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
269
To do that, you can use the ROW_NUMBER() function. In OVER() , you specify the groups into which the rows should be divided ( PARTITION BY ) and the order in which the numbers should be assigned to the rows ( ORDER BY ). You assign the row numbers within each group (i.e., year).
You can use a SELECT command with a GROUP BY clause to group all rows that have identical values in a specified column or combination of columns, into a single row.
It means to group by the first column of your result set regardless of what it's called. You can do the same with ORDER BY .
The TOP 1 means to only return one record as the result set. which record is returned, depends on the column that is specified in the order by clause. If you want to find the record with the minimum value for a particular column, you would query the record with the ORDER BY being ascending (ASC).
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).
79
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
