ORDER BY column name doesn't work

[Martin]

I'm trying to sort the following query by 'latest_id', but the outcome
is wrong.

SELECT id,
(SELECT Max(id) FROM forum_posts WHERE id = t1.id OR refid = t1.id) AS
latest_id
FROM forum_posts t1
WHERE catid = 1
AND refid = 0
ORDER BY 2 DESC

the result is:
id latest_id
35 39
33 34
31 40

while it should be:
id latest_id
31 40
35 39
33 34

How come? And is there another way except sorting by column id? I've
tried the following without success (generates errors):
ORDER BY (SELECT Max(id) FROM forum_posts WHERE id = t1.id OR refid =
t1.id) DESC
and
ORDER BY latest_id DESC

Thanks for help!
/Martin

 

[Martin]

Nobody seen this before?

/Martin

 

[Tom Ellison]

Dear Martin:

I think you should make your query, without the ORDER BY clause, into
a subquery, then select and sort that in the outer query:

SELECT id, latest_id FROM (
SELECT id, Max(id) FROM forum_posts
WHERE id = t1.id OR refid = t1.id)
AS latest_id
FROM forum_posts t1
WHERE catid = 1 AND refid = 0) x
ORDER BY latest_id

Please let me know if this helped.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Martin]

Thank you for helping me out, Tom! Unfortunately, the query you
provided didn't generate more than errors. I'm currently trying to
bone it out to see what you tried to do, but so far, no luck...

/Martin

 

[Tom Ellison]

Dear Martin:

Well, I usually get pretty close. When I don't have your database I
can't see what the error is so readily. We could play ping-pong,
batting this back and forth as you tell me the error and I try to fix
it. Or maybe you'll get it by yourself.

Good luck!

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts