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:
>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

Thread
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