SQL Noob

[GoobTheNoob]

I have a table that contains a historical record of object states. Each record has a time stamp for when that object state record was created. I need to be able to query the "latest" object state record for a given object. I have been trying it with subqueries but they are way too slow:

SELECT * FROM MyObjects WHERE ObjectID = '37' AND TStamp = (SELECT MAX(TStamp) FROM MyObjects WHERE ObjectID = '37')

or

SELECT * FROM MyObjects WHERE ObjectID = '37' AND TStamp >= ALL (SELECT TStamp FROM MyObjects WHERE ObjectID = '37')

I'm pretty new to SQL so I suspect I'm missing something. Help is appreciated. Thanks

[Davey]

Do you have indexes on TStamp and ObjectID? Is ObjectID really a string?

[GoobTheNoob]

I created the indexes and it did improve speed, whether it's fast enough is yet to be determined. Yeah the ObjectID needs to be a string. Thanks for the suggestion.

[mmetzger]

Why don't you do something like:


SELECT TOP 1 * FROM MyObjects WHERE ObjectID = '37' ORDER BY TStamp DESC


That's SQL Server syntax, but should be pretty close for any other sql type..