Getting the last record

[Dave Rudolf]

Hey all,

I'm displaying data from an Access MDB file using an ODBC driver, and I want
to get the most recent record from a table that has a date field. So, I can do
a standard SQL-style select statement (ordered by the date field), and loop to
the last record, but I'd rather not have to pull across the entire table (or
even a block of it) if I only need one record. Does anyone know of a way to do
this?

Alternatively, if I knew how to reverse the sorting I suppose I could just
fetch the first record, so if anyone can tell me how to do that, I'd be in
better position.

Thanks.

Dave

 

[Brendan Reynolds]

I have only tested the following within Access. I believe they should work
via ODBC also, but I have not tested that.

SELECT Top 1 Table1.*
FROM Table1
ORDER BY Table1.TestDate DESC;

The following will also work, but I suspect the former may be more efficient
....

SELECT Table1.*
FROM Table1
WHERE (((Table1.TestDate)=(SELECT Max([TestDate]) FROM Table1)));