Which has the best performance?

[Robin Tucker]

This:

SELECT MAX(TheDate) FROM MyTable

or this:

SELECT TOP 1 TheDate FROM MyTable ORDER BY TheDate DESC
As a follow up question to save me having to post, if I want a different
field from the result set of a MAX query, how do I do it? ie. I want the
"Condition" field of the record with the most recent date. I have been
doing it like this:

SELECT TOP 1 Condition FROM MyTable ORDER BY TheDate DESC

but if MAX(TheDate) is quicker, I would like to SELECT TOP 1 Condition ....
where TheDate is the max date...... Hope this makes sense.....

Basically, I'm going to be performing this query nested inside another query
and I want the maximum performance possible (indexes are a different
question), which means trying to avoid table scans....

[Stu]

You could do

SELECT TOP 1 Condition
FROM MyTable
WHERE TheDate = (SELECT MAX(TheDate) FROM MyTable)

A lot of the performance will depend on your indexes, though.

I hope that helps.

Stu

[Greg D. Moore \(Strider\)]

"Robin Tucker" <id************ *************@r eallyidont.com> wrote in
message news:d8******** ***********@new s.demon.co.uk.. .

This:

SELECT MAX(TheDate) FROM MyTable

or this:

SELECT TOP 1 TheDate FROM MyTable ORDER BY TheDate DESC
As a follow up question to save me having to post, if I want a different
field from the result set of a MAX query, how do I do it? ie. I want the
"Condition" field of the record with the most recent date. I have been
doing it like this:

SELECT TOP 1 Condition FROM MyTable ORDER BY TheDate DESC

but if MAX(TheDate) is quicker, I would like to SELECT TOP 1 Condition ..... where TheDate is the max date...... Hope this makes sense.....
Probably Select MAX, but really the only way to tell is to try it on your
schema and look at the plans, etc.

In addition,as TOP 1 is T-SQL specific, I'd be much more likely to go with
the MAX option.


Basically, I'm going to be performing this query nested inside another query and I want the maximum performance possible (indexes are a different
question), which means trying to avoid table scans....