Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2004
    Posts
    9

    Question Unanswered: Found the solution but do'nt know how to implement

    Hi,
    I wrote the query below and everything works fine except that the rank does not display correctly in the case of a tie. The calculated rank after the ties would not be correct due to this code in the query:

    Code:
    (SELECT COUNT(*) + 1
           FROM History H INNER JOIN
                Lifter L ON L.uidLifter = H.uidLifterRef
           WHERE H.ScoreTotal > History.ScoreTotal AND 
                L.Gender = Lifter.Gender) AS Rank
    The query currently resembles something that in short look like this:

    1 AAAA 100
    2 BBBB 90
    3 CCCC 85
    3 DDDD 85
    5 EEEE 80


    I would like to see:

    1 AAAA 100
    2 BBBB 90
    3 CCCC 85
    3 DDDD 85
    4 EEEE 80


    I found a solution to the problem, in the link below, but I must admit that I have no clue on how to implement that solution in my query to solve the problem. I know that I have to write two small queries that will be used in this larger one.

    Here is the link to the solution : http://support.microsoft.com/kb/207626/EN-US/
    The database can be downloaded from http://www.reitzgh.co.za/add/Database.zip

    Make sure to update the MeetDate in the Meet table to reflect the current date before you try to run the query.

    Code:
    SELECT (SELECT COUNT(*) + 1
           FROM History H INNER JOIN
                Lifter L ON L.uidLifter = H.uidLifterRef
           WHERE H.ScoreTotal > History.ScoreTotal AND 
                L.Gender = Lifter.Gender) AS Rank, 
        Lifter.LifterNumber AS LifterNumber, 
        Lifter.Lastname AS Lastname, Lifter.Firstname AS Firstname, 
        Lifter.BodyWeight, Class.Description AS CDescription, 
        Activity.Description AS GDescription, A.Name AS NameA, 
        History.ScoreA, History.SubTotalA, B.Name AS NameB, 
        History.ScoreB, History.SubTotalB, C.Name AS NameC, 
        History.ScoreC, History.SubTotalC, 
        History.ScoreTotal AS ScoreTotal
    FROM Class, Lifter, Team, History, Meet, Activity, Events A, 
        Events B, Events C
    WHERE History.ScoreTotal > 0 AND 
        Class.uidClass = Lifter.uidClassRef AND 
        Lifter.uidTeamRef = Team.uidTeam AND 
        Lifter.uidLifter = History.uidLifterRef AND 
        Team.uidMeetRef = Meet.uidMeet AND 
        Lifter.Gender = Activity.Code AND 
        Meet.uidEventsA = A.uidEvents AND 
        Meet.uidEventsB = B.uidEvents AND 
        Meet.uidEventsC = C.uidEvents AND 
        Team.TeamStatus = 0 AND Lifter.LifterStatus = 0 AND 
        (MeetDate = DATE ())
    GROUP BY History.ScoreTotal, uidClassRef, Lifter.LifterNumber, 
        Lifter.Lastname, Lifter.Firstname, Lifter.BodyWeight, 
        Class.Description, History.WeightScoreC, Lifter.Gender, 
        Activity.Description, Activity.Activity, Lifter.LifterStatus, 
        A.Name, B.Name, C.Name, History.ScoreA, History.ScoreB, 
        History.ScoreC, History.SubTotalA, History.SubTotalB, 
        History.SubTotalC
    HAVING (Activity.Activity = 'GenderStatus') AND 
        (Lifter.LifterStatus = 0)
    ORDER BY Lifter.Gender ASC, History.ScoreTotal DESC, 
        VAL(Class.Description) DESC, Lifter.BodyWeight ASC
    Last edited by Andreb; 01-07-05 at 09:11.

  2. #2
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    I am not smart enough to display sequential ranking numbers in one step either, but was able to do it using Access queries. I based this solution on your example results in a table called GRADE. A database with this solution is attached.

    With a query, create a temporary table of DISTINCT scores. I named the field SCORE in a table named TEMP1. From your example, the table will have four rows:
    100
    90
    85
    80
    Run the query to create the table.
    Next create a select query from this temporary table, adding the field SCORE and another field:
    Ranking:
    (SELECT Count(*) FROM TEMP1 As T2 WHERE T2.SCORE <= TEMP1.SCORE)
    Save this query; you do not need to run it because it will be used by the third and final query.

    The FINAL query produces the results you want. It links the original GRADE table to the ranking values produced by the above select query.

    In summary, this solution requires running two queries to display correct ranking values and uses a temporary table to facilitate creating the sequential numbers to be used for ranking.

    Good luck!
    Jerry
    Attached Files Attached Files

Posting Permissions

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