Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2015

    Unanswered: sorting and summing rank indexes based on sorting from another derived field

    Dear All,
    I am pretty new to MySQL (or SQL of any sort) but I have got quite deep into a project I am working on with lots of late nights and struggling through. I have finally hit a wall with this problem though - i would love some help with syntax and structure of this query.

    I have a table of race results containing individual races results over a whole race series. The individual races are ranked based on a derived field. What I need to do is sum the ranking for each race and present in a table with each race a vertical column with a total rank at the far right. My table has the following fields;

    table results;
    ResultID, RaceID, ComboID, Nlaps, Mins, Secs, Code

    My derived column is (results.Mins*60 + results.Secs)*1000/classes.PYN/results.Nlaps as cTime
    My query for evaluating each race is;

    (results.Mins*60 + results.Secs)*1000/classes.PYN/results.Nlaps as cTime,
    results.Mins, results.Secs, results.Nlaps,
    m1.FirstName as Hfn, m1.LastName as Hln,
    m2.FirstName as Cfn, m2.LastName as Cln,
    classes.Class, boats.SailNo, combos.ComboID,
    results.RaceID, results.Code, results.ResultID
    FROM members m1, members m2, classes, boats, combos, results
    WHERE m1.MemberID=combos.HelmID
    AND m2.MemberID=combos.CrewID
    AND boats.ClassID=classes.ClassID
    AND boats.BoatID=combos.BoatID
    AND combos.ComboID=results.ComboID
    AND results.RaceID='$rid'
    order by cTime;

    I reference other tables to get all race info I need. I am stuck regarding how I make a query that does the above for each race in the series AND sums the individual rankings across all races to provide a series ranking. I want a table of participants with each race ranking in a new column, sorted by the sum of the individual ranks.

    I am using PHP to process and create HTML output, but it seems best to get MySQL to do the work rather than PHP. I don't even know how I'd do it in PHP (i'm learning that too!)
    Can anyone help me? I've tried to include the important bits but keep irrelevant data to a minimum.
    Many thanks in advance,
    PS I apologise if this post doesn't follow the forum guidelines - I'm new to this too!

  2. #2
    Join Date
    Feb 2015

    simplified question

    I realise that a simple example with data would help so I've added one below.
    In essence I have the following example table of race results;

    table results;

    Name RaceNo Time
    Sam 1 34
    Ben 1 27
    Sam 2 29
    Ken 1 30
    Ben 2 32
    Sam 3 35
    Ken 2 33
    Ken 3 38
    Ben 3 33

    RaceNo = up to 16 or so
    Names = up to 50 or so

    I want to get a sorted output as follows;
    Sorted output with rows of races.

    Name R1 R2 R3 Total
    Ben 27 32 33 92
    Sam 34 29 35 98
    Ken 30 33 38 101

    What I really want is;
    Desired output is each race ranked 1-n, with rows ranked overall by sum(rank).

    Name R1 R2 R3 Total
    Ben 1 2 1 4
    Sam 3 1 2 6
    Ken 2 3 3 8

    I have no idea how to approach this. The following will rank a single race, but I don't know how to string all races together into a single query.

    select Name, find_in_set( uTime, (select group_concat(uTime order by uTime) from results where RaceNo=1)) as rank from results where RaceNo=1;

    Can anyone help?

Tags for this Thread

Posting Permissions

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