Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2006
    Posts
    7

    Question Unanswered: SQL Problem...I'm in a pickle

    I have a games table with two fields, Home_Score and Visit_Score.

    I need to find the game with the highest aggregate score. I have no idea how to go about doing this.

    Any help would be greatly appreciated.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select top 1
           gameid
         , game_date
         , home_score + visit_score as aggregate_score
      from games
    order
        by home_score + visit_score desc
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2006
    Posts
    7
    Quote Originally Posted by r937
    Code:
    select top 1
           gameid
         , game_date
         , home_score + visit_score as aggregate_score
      from games
    order
        by home_score + visit_score desc
    Thank You.

    I have the visit_teamID and home_teamID linked to team_id in the Teams Table.
    How would I be able to include the teams who played as well?

    in the WHERE clause I have Teams.Team_ID = Visit_TeamID, but of course It just shows the visiting teams when I excuse the query....
    Last edited by Swole; 12-07-06 at 04:48.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select top 1
           gameid
         , game_date
         , home_score + visit_score as aggregate_score
         , hteam.teamname as home_team
         , vteam.teamname as visit_team
      from games
    inner
      join teams as hteam
        on hteam.team_ID = games.home_teamID  
    inner
      join teams as vteam
        on vteam.team_ID = games.visit_teamID  
    order
        by home_score + visit_score desc
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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