SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Nov 2004
    Location
    Florida
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Need assistance with a Query please

    Hello -

    The problem here is I know what I am wanting to do, yet I havent a good viable clue asto go about getting it done (Never a good thing) - Yet, I do what I can for the just cause in trying to learn.

    What I wish to do:

    I wish to create a "Top 5 High-Scorers List" which lists the name of the user, the userid of the user, and how many highscores the user has.

    the userid and the username is stored in a table called 'user' (its a vbulletin based forum if your familiar with it).

    The gameing details themselves, are stored in a table called 'games' with the field being: highscorerid (which is the same value as the userid).

    What I wish to do, is take the total count of the users highscores, and match it up to his username and userid for output ... Something like:

    1st place with 100 highscores is Username 1
    2nd place with 72 highscores is username 2
    3rd place with 56 highscores is username 3
    4th place with 23 highscores is username 4 .... etc

    The current query I created (dont laugh) is:

    Code:
    SELECT highscorerid, COUNT(1) as NumberofScores FROM games where highscorerid
    is not null GROUP BY highscorerid order by numberofscores desc
    Well, that works, to a degree. It yields the highscorerid, and how many highscores that highscorerid has. But from there, im lost.

    I wish to make this all 1 query, if its possible. The left join/on join/inner join ... etc... 6 hours in and im still at square one. (With much less hair) - The reason I added the 'is not null' is because for some reason, one of the records it pulls without it, the highscorerid is null, which is odd, as he/she is the one with the most highscores (it got that part right).

    Any assistance you can tender to this lost soul would be much appreciated!!!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select userid
         , username
         , count(highscorerid) as NumberofScores 
      from user
    left outer
      join games 
        on userid
         = highscorerid 
    group 
        by userid
         , username 
    order 
        by NumberofScores desc
    limit 5
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Nov 2004
    Location
    Florida
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sweet! Now we have two ways ... I figured it out just a few minutes ago, and this is what I came up with:

    Code:
    SELECT COUNT(*) as numberofhighscores, games.highscorerid, user.username, user.userid
    FROM games 
    INNER JOIN user AS user ON(games.highscorerid = user.userid) 
    GROUP BY games.highscorerid 
    ORDER BY numberofhighscores
    DESC LIMIT 0,5
    Similar Many thanks for your prompt reply.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    similar, but not quite the same

    the INNER join will return only those users which have a score, whereas the LEFT OUTER join will include those with no scores -- not, however, that this will make a big difference if you're after the top 5 scores, but as shown by this thread, sometimes you do want that option

    also, should you ever use another database besides mysql, you will find that your GROUP BY is incomplete, and therefore the query will die on a syntax error -- the GROUP BY must contain all non-aggregate columns in the SELECT list, even though mysql is egregiously non-standard on this point
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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