Announcement

Collapse
No announcement yet.

SQL for count & order by

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • SQL for count & order by

    I have two tables:

    users
    userid
    username

    and

    data
    id
    username

    I need to get the 5 users who have the most records in data with their username in it. Any ideas?

  • #2
    SELECT count(username) as count, username FROM data GROUP BY username ORDER BY count LIMIT 5

    That should give you something like this:

    Code:
    count Username
    10      JohnM
    9        Felix
    6        Jim
    4        Jack
    2        Peter

    [Edited by rangersfan on 10-06-2000 at 08:18 PM]

    Comment


    • #3
      Other than 'ORDERY', it works great, thanks

      Comment


      • #4
        You souldn't even have the username in the data table, just the user id. Then you would do this to get the data:

        Code:
        SELECT count(data.userid) as count, users.username FROM data,users WHERE users.userid = data.userid GROUP BY data.userid ORDER BY count LIMIT 5

        Comment


        • #5
          either way works...

          Comment

          widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.
          Working...
          X