Hi,

Ive got a website which you can add friends to your buddies list. The problem is I wanna rank the top 5 users with the most friends.

The database setup is:-

Friend | WhosFriend | Friend_Since

So in the table list for example it would look like this...

Harry | Bob | November 2004
Bob | Harry | November 2004
Harry | Trevor | November 2004
Harry | Stephen | October 2004

How would I get it to display that harry has 3 friends and bob has 1 to display in a table?

Most Friends

Harry - 3 Friends
   Bob - 1 Friend

Stephen - 0 Friends

Im not sure if it is possible but any help would be grately appreciated 🙂

    SELECT COUNT(*), Friend FROM friend_table GROUP BY Friend

      Originally posted by devinemke 

      SELECT COUNT(*), Friend FROM friend_table GROUP BY Friend

      [/B]

      Weyhey Excellent Devinemke 😃 works a treat

      Just 1 more thing if I may :o Is there a way to sort this by most friends?

      Ah no worries, Just figured it out 🙂

      it was

      "SELECT COUNT(*), friend FROM friends GROUP BY friend ORDER BY 'COUNT(*)' DESC";

      Thanks for the quick help 🙂

        Although this thread is already resolved, I have a couple of notes regarding the SQL queries and the resolving query, in this case.

        First, aggregate function (SUM, COUNT, AVG etc.) columns are easier to handle in non-SQL code when given alias names using AS and some name. Compare these two pieces of PHP, for example:

        $total_friends = $row['COUNT(*)'];

$total_friends = $row['total_friends'];

        The latter one is much clearer to the coder than the first one.

        Second, ORDER BY accepts alias names since ordering is done after the columns have been selected. And even if for some reason it wouldn't accept an alias, there is no need to use single quotes around the column name.

        So, I'd change the query to this, which is much clearer to me:

        SELECT friend, COUNT(*) AS total_friends 
FROM friends 
GROUP BY friend 
ORDER BY total_friends DESC
          Write a Reply...