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

Have you tried following instructions at http://stackoverflow.com/questions/9618559/select-top-record-for-each-year ? I believe that TOP OVER PARTITION BY is basically the pattern you need here. — Eterm, Feb 25 '15 at 15:01
The last entry could be determined by either the newest CreatedDate or the biggest UID for each unique DocumentID. — Hannah, Feb 25 '15 at 19:30
Does this answer your question? [Select first row in each GROUP BY group?](https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group) — Vadzim, Jun 14 '20 at 10:34
Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) — Michael Freidgeim, May 17 '22 at 13:20

Vadim Loboda · Accepted Answer · 2020-11-27T17:58:48.340

57

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

edited Nov 27 '20 at 17:58

answered Feb 25 '15 at 19:23

Vadim Loboda

2,431
27
44

This seems to be exactly what I needed. Thank you so much!! Thanks everyone else for taking the time to look and comment too x – Hannah Feb 25 '15 at 20:10
1

Thanks Vadim, this helped me as well – Arun Thomas Jul 01 '19 at 12:45
1

This is a lovely solution! – Rory Jan 20 '22 at 13:15
Is this a performance conscious solution as well? – sonicblis Apr 25 '23 at 14:21

score 10 · Answer 2 · edited Sep 08 '17 at 21:53

10

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

edited Sep 08 '17 at 21:53

Marianna S.

905
6
7

answered Feb 25 '15 at 15:08

VB1

204
1
7

"I want to retrieve the last entry for each unique DocumentID" - this will bring back the highest document ID per UID, not what was originally requested! – Dibstar Feb 25 '15 at 15:09
You are right, my bad I read it the other way. I changed the Partition and this should work. I like using window functions as they are more efficient. Hope this helps. – VB1 Feb 25 '15 at 15:22

score 1 · Answer 3 · answered Feb 25 '15 at 15:03

This gives each record a row, taking each document ID and then giving the latest created_date a row_number of 1, and each row before that an increment of 1. We then select the records with a rowno of 1 to get the latest created date per document ID:

SELECT [UID]
,[RecordStatusID]
,[CreatedDate]
,[CreatedTime]
,[CreatedByID]
,[OperationType]
,[InventoryLocationID]
,[DocumentTypeID]
,[DocumentID]
,[SOJPersonnelID]
,[InventorySerialisedItemID]
,[TransactionQty]
,[TransactionInventoryStatusID]
,[Completed]
,[CreatedByType]
,[RecordTimeStamp]
FROM
(
SELECT  
[UID]
,[RecordStatusID]
,[CreatedDate]
,[CreatedTime]
,[CreatedByID]
,[OperationType]
,[InventoryLocationID]
,[DocumentTypeID]
,[DocumentID]
,[SOJPersonnelID]
,[InventorySerialisedItemID]
,[TransactionQty]
,[TransactionInventoryStatusID]
,[Completed]
,[CreatedByType]
,[RecordTimeStamp]
,ROW_NUMBER() OVER (PARTITION BY DOCUMENT_ID ORDER BY CreatedDate) DESC AS ROWNO
FROM [CPData].[dbo].[InventoryAllocations] 
)
WHERE ROWNO = 1

score 0 · Answer 4 · answered Feb 25 '15 at 15:03

Basically like this.

with cte as
(
    SELECT [UID]
        , [RecordStatusID]
        , [CreatedDate]
        , [CreatedTime]
        , [CreatedByID]
        , [OperationType]
        , [InventoryLocationID]
        , [DocumentTypeID]
        , [DocumentID]
        , [SOJPersonnelID]
        , [InventorySerialisedItemID]
        , [TransactionQty]
        , [TransactionInventoryStatusID]
        , [Completed]
        , [CreatedByType]
        , [RecordTimeStamp]
        , ROW_NUMBER() over (partition by DocumentID order by DocumentID desc) as RowNum
    FROM   [CPData].[dbo].[InventoryAllocations]
)

select [UID]
    , [RecordStatusID]
    , [CreatedDate]
    , [CreatedTime]
    , [CreatedByID]
    , [OperationType]
    , [InventoryLocationID]
    , [DocumentTypeID]
    , [DocumentID]
    , [SOJPersonnelID]
    , [InventorySerialisedItemID]
    , [TransactionQty]
    , [TransactionInventoryStatusID]
    , [Completed]
    , [CreatedByType]
    , [RecordTimeStamp]
from cte
where RowNum = 1
order by DocumentID desc

By not sorting by a date in the row_number clause you can't be confident that you will get the right results, or even consistent results. — Dibstar, Feb 25 '15 at 15:10
The results would be consistent, just not what the OP wanted. It should have been ordered by RecordTimeStamp. — Sean Lange, Feb 25 '15 at 15:13

SQL SELECT TOP 1 FOR EACH GROUP

4 Answers4