I have a table with a datefield as part of the key. This was needed because it stores historical information. I need to know how to write an SQL query that will supress all records except those with the “latest” date.
The other part of the key is a field named “Trailer Number”. I only need to see the trailers with the latest dates (because there are multiple entries for each trailer).
You need to apply the MAX function to the date while using the table twice. You can accomplish this by having a self-referencing query using the table twice (MyTable A, MyTable B) or by making a subquery in the WHERE Clause.
(WHERE DATE > (SELECT MAX(DATE) FROM MyTable)