Obtaining the latest record by date

Hi,

I have two tables, one stores items, and another one that stores history for the items from first table. They are related by a foreign key from items table.

In one of my queries, I need to obtain the latest history entry from table 2. For this I use MAX(RecordDate) aggregate. Another way that I know is:

SELECT TOP(1) RecordDate, HistoryID, ....
FROM ProductHistory
WHERE (SerialNumber = '20070101000010')
ORDER BY RecordDate DESC, HistoryID DESC

The query is part of a larger query where I obtain more data from other tables.

The reason I prefer the second option is that, at times I have multiple entries for the same item on the same date (no time info). In such cases, the data returned is the latest entry (HistoryID) in to the system of the rows with same date, thanks to ORDER BY clause. Another reason is that I can select any columns, whereas in the first query type I need to use subquery to obtain other fields after I find my relevant row.

My question is, is there any problem using the second method that I am not aware of? Is this method reliable (TOP(1))? I appreciate if you can guide me to the right method. Thanks!

You're absolutely fine to use TOP in the way that you have done.

The most common mistake when using TOP is to omit an ORDER BY clause where it should really be included, which can make the results unpredicatable and inconsistent. As you have included an ORDER BY clause then you'll be fine.

Chris

|||

Thank you for your answer Chris. My main concern was whether TOP was applied before ORDER BY or to the result of the query at the end. It seems it is applied after all the filters are applied, showing only the portion of the result matching the whole query. Please corrent me if I am wrong. I will mark your response as the answer.

Migrant