At 2:18 +0100 3/15/02, Laban wrote:
>I need a query that counts the number of posts each user have made in my
>forum and then sort the userlist by that. How?
>id, int (post id)
>thread, int (which forum the post belong to)
>poster, int (Who posted. ID from users table)
>string, text (post text)
>category, int (forum category)
>timestamp, int (timestamp)
>SELECT poster, COUNT(*) AS num FROM posts GROUP BY poster ORDER BY num DESC;
>this showes how many posts each user has, sorted by the number of posts.
>Most posts come first.
>Now, how do I implent this in the following query?
>SELECT * FROM users ORDER BY ?????????????
>"poster" is the ID from the user table.
Are you saying that you want to sort the list of postings according
to who has made the most postings?
CREATE TEMPORARY TABLE pcount
SELECT id, COUNT(*) AS num FROM posts GROUP BY id;
SELECT users.* FROM users, pcount
WHERE users.id = pcount.id
ORDER BY pcount.num;