Tricky SQL query

[Mezza]

Ok, heres the problem, any help would be greatly appriciated.

On most forums, you will notice a listbox allowing the user to order the threads/posts by each of the thread/post headings (title, author, views etc)

I wish to be able to order a list of threads (on a piece of forum software i am writing) by Posts and Latest Post

ie order the threads by how many posts they have, or by which has the newest post.

I have two tables in my database, Threads and Posts.

1: How can i order the data i pull out of Threads, by a field in Posts.

heres the tricky one...

2: How can i order the data i pull out of Threads, by how many posts there are with the the threadID

ie, i have 2 threads, and 5 posts, 3 of the posts hold the ID for thread 1 and 2 of the posts hold the ID for thread 2

how do i order these by number of posts. or will i have to create a "posts" field in the threads table.

Thanks in advance.

[nnichols]

SELECT cols, COUNT(Posts.ThreadID) AS c_post
FROM Threads
LEFT JOIN Posts
ON Threads.ID=Posts.ThreadID
GROUP BY Posts.ThreadID
ORDER BY c_post DESC

This will work but it would be more efficient to store the number of posts in the threads table as it will be much quicker when the tables get big, especially if you expect the forum to be busy.

Hope this helps 😉

[Mezza]

Thanks for the help, this works fine. I think I will end up creating new fields in the threads table anyway, but i didnt want a script getting the better of me! 🙂

Thanks again