Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2010
    Posts
    4

    Unanswered: Cant workout this query for the life in me!

    Hi guys, I have a sports database with 3 tables; Player, Player Performance, Matches.

    What I am trying to do is find all the Matches players have played in 2002 only. Then I SUM up the scores for every player from the "Player Performance" table. I then display the player with the highest score in 2002. I have got it to work but now I'm stuck on trying to include MAX in there to find the highest score. Suggestions would be greatly appreciated. Thank you.
    Last edited by JonFrampton; 03-24-10 at 15:18.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    [sigh] not another one

    you would not believe how many different versions of this exact same problem i have seen on various discussion forums over the past few days

    i'm sorry to have to tell you this, but we won't do your assignment for you on this site

    if you want help, you need to make a determined effort on your own first

    post what you have, and we might critique it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2010
    Posts
    4
    Thanks for the reply but I've posted my query above. I'm asking for suggestions on the arithmetic and not asking for someone to do it.

    I was thinking of using subqueries but I've already hardcoded the Joins in my PHP files, so now I'm not sure how to go about doing this. Either I replace the SUM and workout the MAX first.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    hint: ORDER BY record_store DESC LIMIT 1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    hint #2: that won't work when two players have the maximum score
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Mar 2010
    Posts
    4
    I've tried both DESC and LIMIT but they're only superficial, i.e. effect views only. What I'm trying to do is add the MAX function in there.

    I really want my queries and PHP code to stand out, so I've gone from basic recommendation of using subqueries and only INT and VARCHAR to using appropriate variables and JOINS. The only problem I have right now is that I can't workout the logic - should I calculate MAX first and then SUM that up?

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    another hint (the real one): calculate everyone's score, then, using this as a subquery, find the max, then, using that as a subquery, find the person who has this score using a HAVING clause

    that's as far as i'll go

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Mar 2010
    Posts
    4
    Thanks, I just paid another guy to do it for me. It was getting boring anyway.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i do admire persistent and rich students, they make the best entrepreneurs

    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
  •