MSAccess97: SELECT in ORDER BY?

[SuperDre]

I've got the following query:

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

Which works perfectly under MSSQL, but I get a problem with the ORDER BY part when I fire it at a MSAccess..
Doesn't MSAccess (or JET-engine) support a subquery based field in the order by, or am I doing something wrong?

[stlaural]

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

[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.

[stlaural]

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.

Yeah I'm not 100% sure for the optimal thing though maybe access is good enough to know that the query as already run and then not run it again. I also just noticed that you gave an alias to your columns so you could also use that to order :

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

[SuperDre]

Yeah I'm not 100% sure for the optimal thing though maybe access is good enough to know that the query as already run and then not run it again. I also just noticed that you gave an alias to your columns so you could also use that to order :

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

Aliases don't work in the ORDER BY or WHERE part (MS-SQL/MS-Access), I already tried that (which is also one of the Illogic items I was refering too hehe). But I think it really requeries the subquery if used in the WHERE clause..