Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New Coder
    Join Date
    Jul 2011
    Posts
    52
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Order by count of rows

    Hello
    I have database table like that:
    userid|value
    ...
    and the data goes like that:
    1|something
    1|dsfs
    1|ds
    2|ffffew
    2|dfsdf
    etc..

    I want to make SQL query that will order this results by the user with the most values
    if userid 1 has 6 rows included his id, and it's the highest (no more users with more than 6 rows) it will show him first.

    I am using PHP.

    --Thanks in advance
    Need domain? Buy Cheap Domain at Rapid Host :)

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,652
    Thanks
    80
    Thanked 4,638 Times in 4,600 Posts
    Code:
    SELECT userid, COUNT(value) AS howmany
    GROUP BY userid
    ORDER BY howmany DESC
    LIMIT 1
    The LIMIT can be any number you want. With LIMIT 1 you will only get the user with the most values. But CAUTION: What if you have 3 users all with the same top number of values??
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •