<p>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.</p>

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

<pre><code>Select * from HistoryTable where BatchRef = @BatchRef
</code></pre>

<p>It will return all the steps for all the items in the batch - eg</p>

<pre>
<b>Id      Status  BatchRef        ItemCount</b>
1       1       Batch001        100
1       2       Batch001        110
2       1       Batch001        60
2       2       Batch001        100
</pre>

<p>But what I really want is:</p>

<pre>
<b>Id      Status  BatchRef        ItemCount</b>
1       2       Batch001        110
2       2       Batch001        100
</pre>

<p>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</p>

[{'answer_id': 29848, 'author': 'ZombieSheep', 'author_id': 377, 'author_profile': 'https://Stackoverflow.com/users/377', 'pm_score': 3, 'selected': False, 'text': '<p>Assuming you have an identity column in the table...</p>\n\n<pre><code>select \n    top 1 &lt;fields&gt; \nfrom \n    HistoryTable \nwhere \n    BatchRef = @BatchRef \norder by \n    &lt;IdentityColumn&gt; DESC\n</code></pre>\n'}, {'answer_id': 29853, 'author': 'Matt Hamilton', 'author_id': 615, 'author_profile': 'https://Stackoverflow.com/users/615', 'pm_score': 0, 'selected': False, 'text': "<p>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:</p>\n\n<pre><code>with LastBatches as (\n    select Batch, max(Id)\n    from HistoryTable\n    group by Batch\n)\nselect *\nfrom HistoryTable h\n    join LastBatches b on b.Batch = h.Batch and b.Id = h.Id\n</code></pre>\n\n<p>Or a subquery (assuming the group by in the subquery works - off the top of my head I don't recall):</p>\n\n<pre><code>select *\nfrom HistoryTable h\n    join (\n        select Batch, max(Id)\n        from HistoryTable\n        group by Batch\n    ) b on b.Batch = h.Batch and b.Id = h.Id\n</code></pre>\n\n<p>Edit: I was assuming you wanted the last item for <em>every</em> 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.</p>\n"}, {'answer_id': 29861, 'author': 'Henrik Gustafsson', 'author_id': 2010, 'author_profile': 'https://Stackoverflow.com/users/2010', 'pm_score': 0, 'selected': False, 'text': '<p>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 </p>\n\n<pre><code>SELECT TOP 1 ...\n</code></pre>\n\n<p>if you\'re using MSSQL 2k or earlier, or the SQL compliant variant</p>\n\n<pre><code>SELECT * FROM (\n  SELECT\n    ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,\n    columns\n  FROM tablename\n) AS foo\nWHERE rownumber = n\n</code></pre>\n\n<p>for any other version (or for other database systems that support the standard notation), or</p>\n\n<pre><code>SELECT ... LIMIT 1 OFFSET 0\n</code></pre>\n\n<p>for some other variants without the standard SQL support. </p>\n\n<p>See also <a href="https://stackoverflow.com/questions/16568/how-to-select-the-nth-row-in-a-sql-database-table">this</a> 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.</p>\n'}, {'answer_id': 29862, 'author': 'Mark Brackett', 'author_id': 2199, 'author_profile': 'https://Stackoverflow.com/users/2199', 'pm_score': 4, 'selected': True, 'text': "<p>It's kind of hard to make sense of your table design - I think SO ate your delimiters.</p>\n\n<p>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 <em>think</em> that the GROUP BY would be BatchRef and ItemCount, and Id will be your unique column.</p>\n\n<p>Then, join back to the table to get all columns. Something like:</p>\n\n<pre><code>SELECT * \nFROM HistoryTable\nJOIN (\n   SELECT \n       MAX(Id) as Id.\n       BatchRef,\n       ItemCount\n   FROM HsitoryTable\n   WHERE\n       BacthRef = @batchRef\n   GROUP BY\n       BatchRef,\n       ItemCount\n ) as Latest ON\n   HistoryTable.Id = Latest.Id\n</code></pre>\n"}, {'answer_id': 36696, 'author': 'JMcCon', 'author_id': 3677, 'author_profile': 'https://Stackoverflow.com/users/3677', 'pm_score': 1, 'selected': False, 'text': '<p>Assuming the Item Ids are incrementally numbered:</p>\n\n<pre><code>--Declare a temp table to hold the last step for each item id\nDECLARE @LastStepForEach TABLE (\nId int,\nStatus int,\nBatchRef char(10),\nItemCount int)\n\n--Loop counter\nDECLARE @count INT;\nSET @count = 0;\n\n--Loop through all of the items\nWHILE (@count &lt; (SELECT MAX(Id) FROM HistoryTable WHERE BatchRef = @BatchRef))\nBEGIN\n    SET @count = @count + 1;\n\n    INSERT INTO @LastStepForEach (Id, Status, BatchRef, ItemCount)\n        SELECT Id, Status, BatchRef, ItemCount\n        FROM HistoryTable \n        WHERE BatchRef = @BatchRef\n        AND Id = @count\n        AND Status = \n        (\n            SELECT MAX(Status) \n            FROM HistoryTable \n            WHERE BatchRef = @BatchRef \n            AND Id = @count\n        )\n\nEND\n\nSELECT * \nFROM @LastStepForEach\n</code></pre>\n'}, {'answer_id': 7949403, 'author': 'Ram_God', 'author_id': 1020241, 'author_profile': 'https://Stackoverflow.com/users/1020241', 'pm_score': 1, 'selected': False, 'text': '<pre><code>SELECT id, status, BatchRef, MAX(itemcount) AS maxItemcount \nFROM HistoryTable GROUP BY id, status, BatchRef \nHAVING status &gt; 1 \n</code></pre>\n'}]
['https://Stackoverflow.com/questions/29847', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/1075/']