SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Thread: Query problem

  1. #1
    SitePoint Zealot
    Join Date
    Mar 2003
    Location
    Halifax
    Posts
    191
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Query problem

    Can anyone help me out, I've written a query to return all players from a table based on a few parameters and want to calculate a total average, and total rank from a number of records over a set time period

    The query I am using is this:
    Code:
    SELECT r.shortname, c.shortname AS club, ph.playerid, p.firstname, p.lastname, ROUND(AVG(index_final), 2) AS average, ROUND(AVG(rank), 2) AS rank
    FROM `club_region` cr
    LEFT JOIN `region` r ON r.regionid = cr.regionid
    LEFT JOIN `club` c ON c.clubid = cr.clubid
    LEFT JOIN `player_hist` ph ON ph.clubid = c.clubid
    LEFT JOIN `club_comp_season` ccs ON ccs.clubid = ph.clubid
    LEFT JOIN `season` s ON s.seasonid = ccs.seasonid
    LEFT JOIN `player` p ON p.playerid = ph.playerid
    LEFT JOIN `actim_pers_rank` a ON a.playerid = p.playerid
    WHERE cr.regionid = 7 
    AND ccs.compid = 1
    AND ccs.seasonid = 36
    GROUP BY a.playerid
    ORDER BY a.rank ASC
    The code pretty much works, my only problem comes when I view the results. The results are not ordering properly, for instance I get a results with a rank value of 11.02 above a value with 8.27.

    The option to get round this, is to split it in to 2 queries and I know this works.

    However by splitting it in to two seperate queries I then have minor issues with either looping through the results and hitting the db with individual queries, appending these in to the array and then sorting which just doesn't seem to be efficient.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    last time i checked, 11.02 was above 8.27

    are you saying they are sorted alphabetically instead of numerically?

    and how would splitting the query solve an ORDER BY problem?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Mar 2003
    Location
    Halifax
    Posts
    191
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Seems stupid I know, I've amended the query slightly and got everything working.


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
  •