Get the last item in a table - SQL

Question

I have a history table in SQL Server that basically tracks an item through a process. An element has some fixed fields that do not change throughout the process, but has several other fields, including status and identifier, which increase as the process steps increase.

Basically, I want to get the last step for each item specified in the Batch Reference. Therefore if i do

Select * from HistoryTable where BatchRef = @BatchRef 

It will return all steps for all elements in the package - for example

  Id Status BatchRef ItemCount
 1 1 Batch001 100
 1 2 Batch001 110
 2 1 Batch001 60
 2 2 Batch001 100

But I really want:

  Id Status BatchRef ItemCount
 1 2 Batch001 110
 2 2 Batch001 100

Edit: Appologies - it doesn't look like the TABLE tags work with Markdown - followed the email help and look great in the preview

Answer 1

It's hard to understand your table design - I think you ate your dividers.

The main way to handle this is GROUP BY with your fixed fields and select MAX (or MIN) for some unqiue value (lifetime usually works well). In your case, I think GROUP BY will be BatchRef and ItemCount, and Id will be your unique column.

Then join the table to get all the columns. Sort of:

 SELECT * FROM HistoryTable JOIN ( SELECT MAX(Id) as Id. BatchRef, ItemCount FROM HsitoryTable WHERE BacthRef = @batchRef GROUP BY BatchRef, ItemCount ) as Latest ON HistoryTable.Id = Latest.Id 

Answer 2

Assuming the table has an identifier column ...

 select top 1 <fields> from HistoryTable where BatchRef = @BatchRef order by <IdentityColumn> DESC 

Answer 3

Assuming Element Identifiers are incrementally numbered:

 --Declare a temp table to hold the last step for each item id DECLARE @LastStepForEach TABLE ( Id int, Status int, BatchRef char(10), ItemCount int) --Loop counter DECLARE @count INT; SET @count = 0; --Loop through all of the items WHILE (@count < (SELECT MAX(Id) FROM HistoryTable WHERE BatchRef = @BatchRef)) BEGIN SET @count = @count + 1; INSERT INTO @LastStepForEach (Id, Status, BatchRef, ItemCount) SELECT Id, Status, BatchRef, ItemCount FROM HistoryTable WHERE BatchRef = @BatchRef AND Id = @count AND Status = ( SELECT MAX(Status) FROM HistoryTable WHERE BatchRef = @BatchRef AND Id = @count ) END SELECT * FROM @LastStepForEach 

Answer 4

 SELECT id, status, BatchRef, MAX(itemcount) AS maxItemcount FROM HistoryTable GROUP BY id, status, BatchRef HAVING status > 1 

Answer 5

It's a little difficult to decrypt your data in the way that WMD formatted it, but you can use the trick you need with regular table expressions in SQL 2005:

 with LastBatches as ( select Batch, max(Id) from HistoryTable group by Batch ) select * from HistoryTable h join LastBatches b on b.Batch = h.Batch and b.Id = h.Id 

Or a subquery (assuming that the group in the subqueries works - I don’t remember from the top of the head):

 select * from HistoryTable h join ( select Batch, max(Id) from HistoryTable group by Batch ) b on b.Batch = h.Batch and b.Id = h.Id 

Edit: I assumed that you need the last item for each batch. If you just need this for one batch, then the other answers (do the top 1 and arrange the descending ones) is the way to go.

Answer 6

As already suggested, you probably want to change the order of the request to sort it in the other direction so that you actually load the first line. Then you probably want to use something like

 SELECT TOP 1 ... 

if you use MSSQL 2k or earlier, or an option compatible with SQL

 SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber, columns FROM tablename ) AS foo WHERE rownumber = n 

for any other version (or other database systems that support standard notation) or

 SELECT ... LIMIT 1 OFFSET 0 

for some other options without standard SQL support.

See also this question for some further discussion on row selection. Using the aggregate function max () may or may not be faster depending on whether you need to calculate a value to scan the table.