List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:March 15 2002 2:33am
Subject:Re: Query
View as plain text  
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?
>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?

SELECT id, COUNT(*) AS num FROM posts GROUP BY id;

SELECT users.* FROM users, pcount
ORDER BY pcount.num;


QueryLaban15 Mar
  • Re: QueryPaul DuBois15 Mar
Re: QueryChris Becker15 Mar
  • Insert from selectDavid McInnis16 Mar
    • Re: Insert from selectKittiphum  Worachat16 Mar
    • Re: Insert from selectGeorg Richter16 Mar