Get last item in a table - SQL

Question

I have a History Table in SQL Server that basically tracks an item through a process. The item has some fixed fields that don't change throughout the process, but has a few other fields including status and Id which increment as the steps of the process increase.

Basically I want to retrieve the last step for each item given a Batch Reference. So if I do a

Select * from HistoryTable where BatchRef = @BatchRef

It will return all the steps for all the items in the batch - eg

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

But what I really want is:

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

Edit: Appologies - can't seem to get the TABLE tags to work with Markdown - followed the help to the letter, and looks fine in the preview

Answer 1

Assuming you have an identity column in the table...

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

Answer 2

It's a bit hard to decypher your data the way WMD has formatted it, but you can pull of the sort of trick you need with common table expressions on 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 the group by in the subquery works - off the top of my head I don't recall):

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 was assuming you wanted the last item for every batch. If you just need it for the one batch then the other answers (doing a top 1 and ordering descending) are the way to go.

Answer 3

As already suggested you probably want to reorder your query to sort it in the other direction so you actually fetch the first row. Then you'd probably want to use something like

SELECT TOP 1 ...

if you're using MSSQL 2k or earlier, or the SQL compliant variant

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 for other database systems that support the standard notation), or

SELECT ... LIMIT 1 OFFSET 0

for some other variants without the standard SQL support.

See also this question for some additional discussion around selecting rows. Using the aggregate function max() might or might not be faster depending on whether calculating the value requires a table scan.

Answer 4

It's kind of hard to make sense of your table design - I think SO ate your delimiters.

The basic way of handling this is to GROUP BY your fixed fields, and select a MAX (or MIN) for some unqiue value (a datetime usually works well). In your case, I think that the GROUP BY would be BatchRef and ItemCount, and Id will be your unique column.

Then, join back to the table to get all columns. Something like:

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 5

Assuming the Item Ids 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