Finding the most recent record in a table with otherwiseidentical field values

[kbergstrom]

Hello,

Would anyone be able to share how they find the MOST recent record among otherwise identical records in for example a transaction table?

[jdeline]

If you have an autonumber id in your table, do a SELECT MAX(id) AS maxId FROM
myTable. Then use maxId in the WHERE clause of your query to get the most
recent record. Of course, you can combine this into one query.

[Dan Bracuk]

If you don't have a lastupdatedat date/time field, and you can add one, do so.

Or, assuming you have a single field primary key
select max(primarykeyfield)
from thetable
where etc.

make your where clause as long as you can.

[SQLMenace]

Assuming you have no indexes on your table
if you do a select * from tableA then the first record should be the first
record inserted etc
Remember no indexes and you can not sort the table

[url]http://sqlservercode.blogspot.com/[/url]

[kbergstrom]

I must be doing something wrong. In a series of transactions for the same item
within a given table, I want to see the entire record for that item that is
most recent. I also want to see the most recent record for all other items in
that same table as well which will have a different transaction ID. The Max
function seems to return only a single value when selecting on transaction_ID.
Any other ideas?

[mxstu]

I'm not sure what your table structure is, but if your data looks something
like this ....

RecordID | ItemID | Other Fields
----------------------------------
1 | 31 | stuff...
----------------------------------
2 | 31 | stuff...
----------------------------------
3 | 31 | stuff...
----------------------------------

And you want to pull information for the most recent record for "itemID = 31",
you would need one query to get the max record for each itemID:

SELECT ItemID, MAX(RecordID) AS MaxRecordID
FROM yourTable
GROUP BY ItemID

... and then join those results back to the same table to get the details.
This could be done in a single query. If you need to perform this operation
frequently, I would suggest creating a "view" in your database that returned
the MaxRecordID for each item.

[kbergstrom]

Yes, that works for a single item ID. I was looking for something that would
return the latest records for all item ID's. I have almost 20,000 items in that
table, with over 76,000 transactions. What I want returned is the 20,000 most
recent transactions for those items.

I am not familiar with creating the view that you mentioned for all 20,000
items. Is there some guidance online that you can point me to?

[mxstu]

Yes. The sql statement I posted would give you the most recent transactions
for all items in your table (assuming there are no syntax errors). Although
you might need to use a LEFT JOIN if you want to include records that have (0)
transactions.

A "view" is just a virtual table. It allows you to present information in a
different way without creating a separate table.
[url]http://www.w3schools.com/sql/sql_view.asp[/url]

If you plan to do this frequently, I would suggest creating a "view" that
returned the max transaction for all items. You could then join to the "view"
in your query. So instead of using this ...

SELECT i.RecordID, i.ItemID, i.OtherFields
FROM yourTable i INNER JOIN
( SELECT ItemID, MAX(RecordID) AS MaxRecordID
FROM yourTable
GROUP BY ItemID
) as maxI
ON i.RecordID = maxI.RecordID

You would join to the view ....

SELECT i.RecordID, i.ItemID, i.OtherFields
FROM yourTable i INNER JOIN getMaxTransactionIDView
ON i.RecordID = maxI.RecordID