At 2:18 +0100 3/15/02, Laban wrote: >Hi > >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? > >table posts >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; > >//Laban --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
