MSAccess97: SELECT in ORDER BY?

[SuperDre]

I don't think this is optimal anyway, as it runs the same query two times. Why don't you just order by columns number, this way :

SELECT a.MyID
,(SELECT MAX(b.MyDate) FROM MyTable2 b WHERE b.MyID=a.MyID) AS MyDateField 
FROM MyTable1 f 
ORDER BY 2 DESC, 1 DESC

haha, didn't even know you could use a fieldnumber in the ORDER BY...

when it comes to optimal I can't think of a reason why the engine would perform the subquery 2 times as it is an ORDER BY and the 'field' is in the SELECT part (but that's just my logic, which seems many times much different as that of Microsoft engineers LOL).

thanx.