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

Nobody seen this before?

/Martin

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

>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


On 14 Nov 2003 07:24:32 -0800, (E-Mail Removed) (Martin)
wrote:

>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

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 <(E-Mail Removed)> wrote in message news:<(E-Mail Removed)>...
> 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

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

On 17 Nov 2003 04:34:01 -0800, (E-Mail Removed) (Martin)
wrote:

>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 <(E-Mail Removed)> wrote in message news:<(E-Mail Removed)>...
>> 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