Latest Date query...

[Guest]

Okay,
Age old problem... Looking for the latest date in a table.

Our team has come up with 2 methods... Both work, but there is debate on
which is more efficient/preferable.

Just looking for thoughts from others that may have done the same thing


Method #1:

SELECT TOP 1 myTable.Datestamp
FROM myTable
WHERE (((myTable.Datestamp) Is Not Null))
ORDER BY myTable.Datestamp DESC;




Method #2

SELECT Max(myTable.Datestamp) AS MaxOfDatestamp
FROM myTable
ORDER BY Max(myTable.Datestamp);


Just looking for pro's and cons to either approach..

 

[John Spencer (MVP)]

Top 1 could return more than one row if there is a tie. That may make very
little difference

I wouldn't bother to use an order by clause in the second query as by definition
it is returning one row

My personal choice would be method 2 based on the GUESS that the aggregate
functions are maximized to get the results quickly.

No evidence either way. With small recordsets I would guess that the difference
would be virtually undetectable.