CODEWITHSUNDEEP
sql-servert-sql
ArtK
ArtK

Reputation: 1185

Selecting group of latest records

I have a table with abstract below
Table_A
(RecordID int PK, LotID int, BatchID int, StatusID int, StatusTime datetime)
Each lot identified by LotID can have multiple batches identified by BatchID and each batch's status is tracked. Each time a status changes, a new record is inserted into Table_A indicating LotID, BatchID, StatusID and StatusTime. I need to filter a single latest record for each batch. Could I do it with single SELECT DISTINCT statement or should I try to JOIN the table on itself, ORDER BY StatusTime and SELECT TOP 1 record for each LotID'+BatchID` set ? How would you take a stab at it in T-SQL?

Upvotes: 0

Views: 49

Answers (1)

Malk
Malk

Reputation: 11983

Create a dataset with a row_number() column to order a subset of data:

;WITH cte AS (
  SELECT
      RecordID
    , LotID
    , BatchID
    , StatusID
    , StatusTime 
    , batchrank = ROW_NUMBER() 
                  OVER( PARTITION BY BatchId
                        ORDER BY StatusTime DESC )
  FROM Table_A
)
SELECT * 
FROM cte
WHERE batchrank = 1

Upvotes: 1

Related Questions