SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Addict
    Join Date
    Nov 2004
    Location
    Canada
    Posts
    373
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Determining rank?

    Hi.

    I currently have a database that I pull team stats out of. Such as points, goals for, goals against, fouls, etc...

    I was looking for the best way to get from MySQL the rank of each team. So for example, ranking the teams based on points, such as:

    Team 1 - 20 points ... would display (Tied for 1st)
    Team 2 - 20 points ... would display (Tied for 1st)
    Team 3 - 18 points ... would display (3rd place)
    Team 4 - 17 points ... would display (4th place)

    and so on...

    What I am using right now is just a select and ordring the results by points. I tried using PHP to give me what I wanted (like check previous result, if its the same then increment a counter, and so on), but I was wondering if there was a way to do this using MySQL? (Im learning every day MySQL can do some wonderful things!)

    This is my MySQL statement I am using:
    Code:
    SELECT t.id
    , t.teamname
    , count(w.id) as gamecount
    , sum(w.team_score) as goalsfor
    , sum(w.opponent_score) as goalsagainst
    , sum(w.home_fouls) as fc
    , sum(w.away_fouls) as tf
    , sum(CASE
    WHEN w.team_score > w.opponent_score THEN 3
    WHEN w.team_score = w.opponent_score THEN 1
    WHEN w.team_score < w.opponent_score THEN 0
    END) as points
    , sum(team_score) - sum(opponent_score) as goaldifferential
    , sum(CASE
    WHEN w.team_score > w.opponent_score THEN 1
    ELSE 0
    END) as wins
    , sum(CASE
    WHEN w.team_score = w.opponent_score THEN 1
    ELSE 0
    END) as draws
    , sum(CASE
    WHEN w.team_score < w.opponent_score THEN 1
    ELSE 0
    END) as losses
    FROM teams as t
    LEFT OUTER JOIN (SELECT id
    , home_id as team_id
    , home_score as team_score
    , away_score as opponent_score
    , home_fouls
    , away_fouls
    FROM weekly
    UNION all
    SELECT id
    , away_id
    , away_score
    , home_score
    , away_fouls
    , home_fouls
    FROM weekly) as w
    on w.team_id = t.id WHERE w.team_score is NOT NULL
    GROUP
    BY t.id
    ,t.teamname
    ORDER BY points DESC, goaldifferential DESC, wins DESC

  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)
    yes, it's possible, but because of the complexity of your query, it would make it a lot more complicated

    checking the previous result while looping over the results is actually a very good and very efficient way to produce a ranking



    by the way, you have this --
    Code:
    FROM teams as t
    LEFT OUTER JOIN ( SELECT ...) as w
    on w.team_id = t.id 
    WHERE w.team_score is NOT NULL
    just drop the WHERE clause and make it an INNER JOIN
    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
  •