Order Query Question

[jewel464g]

Need some assistance guys - I just created a threaded message board, but there is one problem to fix before I am happy with it.

Background, so you will understand...
messageID - this is the field in the db that keeps the messages straight. The first message in the thread is 00001. A response would be 00001-001. Next response would be 00001-002. Response to that message would be 00001-002-001. And so on... There is also a "dateOfPost" field.

I am having no problem sorting the fields by messageID - works great. I thought... When you add a message, messageID is added as the next greatest base messageID (00002 would follow 00001 for example). The Problem - I want the newest base messages to be added to the top, but I want the replies to flow down in ascending dateOfPost order. So how do I order the root messages in dateOfPost order, newest to oldest, while having the replies ordered oldest to newest under each base post?

Here is how I need the messages ordered.

This is me 7/27/2001
Re: This is me 7/28/2001
Re: This is me 9/21/2001
testing 6/21/2001
TESTING 9/3/2001
Re: testing 9/5/2001
Re: testing 9/6/2001

Appreciate any help. If you get this far thanks for reading.

 

[Cagliostro]

select ..... from xxx order by thread, DateOfPost, MessageID John Fill

ivfmd@mail.md

 

[jewel464g]

Got it working with this query. Thanks - when I saw your post it hit me. So simple too...

"Select messageID, subject, dateOfPost, userName, discussionGroup From tblMessage Where discussionGroup = '" & forum & "' Order By Left(messageID, 5) Desc, dateOfPost;"