Post reply

Help with SQl Statement ranking

  • January 3, 2009 at 8:07 pm

    #197988

    I have a table of results for individual races.

    What I would like is a statement that retrieved the results with their ranking according to a where the runner came in that race.

    for simplicity I'm considering adding a field to the table to record the same info permanently, but am willing to be guided on the wisdom of this!?

    The SQL statement to get the times i wish to rank by goes like this:

    Select Results.RaceID, Results.ResultID, Results.MemberID

    Case WHEN Races.sealed = 'True' THEN

    DATEADD(s,-DATEDIFF(s,Results.rHandicap, 0), Results.rTime)

    ELSE

    DATEADD(s,DATEDIFF(s,Results.Handicap,0), Results.rTime)

    END AS [Actual Time]

    So from this I then want to rank each result (perhaps even a quartile will do) within it's respective race

    If i can be guided as to how to restart this ranking within each race or where/what commands i need to investigate that would be appreciated....or else ill simply do it per race & update the new field i'm debating adding1?

    thanks in advance

    Michael

  • January 3, 2009 at 8:49 pm

    #920813

    With the information given, the short answer would be to take a look at ROW_NUMBER OVER (PARTITION BY... ORDER BY...) in Books Online. You could also do similar with RANK or maybe even DENSE_RANK.

    The long answer would be that you could probably get a bit of tested demonstration code if you provided a table creation statement and some readily consummable test data... see the link in my signature for how to easily do that.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 3, 2009 at 9:31 pm

    #920822

    OK... thanks for this & yes those commands seem to be what i was looking for, whilst i appreciate that presenting all the required data tables would result in a "better" answer i prefer to fiddle myself (since i have the time)

    I am looking for a meaningful "placement" within the race results like a percentage. rownumber is meaningless unless i know how many were in the race (guess i could do a count?? and create this myself) quartile might prove enough, but a more detailed "score" of where the participant came might be better

    IS there such a thing or must i "create" it?

  • January 3, 2009 at 10:18 pm

    #920823

    What would you have for such a score? If there are 10 people in a race, isn't first through the 10th place sufficient? That "Partition By" part of the ROW_NUMBER "windowing" function would do that nicely.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 3, 2009 at 11:53 pm

    #920825

    Ok, what i mean is that the row number in & of itsself is meaningless unless you know how many runners were in the race. What I'm looking for is a percentile rather than a quartile (quatile might suffice since it is less work)

    so by example:

    row 1-10 & 10 runners

    row number divided by total to give "percentile"

    1/10 =.1

    2/10=.2

    whereby i have rules that apply penalties or help if the runner finishes in the top 10% or top 20% twice etc...

  • January 4, 2009 at 12:18 am

    #920826

    Forgive me...have to ask....

    I presume you concur it's a bad design idea to include the "percentile" information in the table?

  • January 4, 2009 at 9:17 am

    #920864

    Michael Artz (1/4/2009)

    Forgive me...have to ask....

    I presume you concur it's a bad design idea to include the "percentile" information in the table?

    Heh... why? You planning on changing the results after the races are done? 😉 There's nothing wrong with storing that type of information in the table if it'll never change again.

    So far as the row number thing goes... like I said, ya gotta do the "partition" thing... something like this would do... notice how the partition on the race id will restart the numbering...

    SELECT (ROW_NUMBER() OVER (PARTITION BY r.RaceID, ORDER BY someresultformula)) /100 AS Percentile,

    othercolumnshere

    FROM Results r

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 4, 2009 at 2:26 pm

    #920908

    thanks a million!..... change results??......me???.....NEVER!!! 😉

    the problem with the advised code:

    SELECT (ROW_NUMBER() OVER (PARTITION BY r.RaceID, ORDER BY someresultformula)) /100 AS Percentile,

    othercolumnshere

    FROM Results r

    is that again dividing by a static numeral doesnt give me meaningful data. I need to divide by a "COUNT" of the records returned within that "PARTITION" (which is the same as the highest ROW_NUMBER...i guess)

    but thankyou the above example is very succinct

  • January 4, 2009 at 2:47 pm

    #920914

    Well, then add the COUNT windowing function.

    SELECT (ROW_NUMBER() OVER (PARTITION BY r.RaceID, ORDER BY someresultformula))

    / (COUNT() OVER(PARTITION BY r.RaceID)) AS Percentile,

    othercolumnshere

    FROM Results r

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • February 10, 2009 at 1:03 am

    #941004

    darn... drat... curse....

    sorry this has taken sooooooo long to test!!

    ive tried:

    SELECT row_number() over(ORDER BY [Handicap Time]) as RacePlace, [Actual Time], [Handicap Time]

    FROM dbo.vwResultsActualTime

    ORDER BY [Handicap Time] where RACEID=100

    but got the following error:

    The OVER SQL construct or statement is not supported.

    which is a total lie!!! because in another stored procedure i use the rownumber/over construct as follows:

    WHERE Suppliers.SupplierID = Products.SupplierID) as SupplierName,

    ROW_Number() over (order by productname) as RowRank

    i am sure there's some subtlety i'm missing!!??

    i've even tried "dumbing" the query down to just referencing a table rather than a view...the original sql contained a partition by statment as well which ive deleted for testing!??

    could this be lacking functionality in sql express??

    is it possible to use this construct in a stored procedure but not a query??

    im totally lost....

    thank you again

    Michael

  • February 10, 2009 at 3:53 am

    #941067

    OK...got this to work (partially):

    SELECT

    (row_number() over(partition by RaceID order by [Actual Time])

    )as RacePlace,

    (count([Actual Time]) over(partition by RaceID)

    ) as raceCount,

    CAST(

    1.*(row_number() over(partition by RaceID order by [Actual Time])

    /

    (count([Actual Time]) over(partition by RaceID))

    )*100 as decimal(5,2)

    ) as PercPlace

    FROM vwResultsActualTime

    note the lack of apostrophe in the over clause

    this results in the following results:

    22 27 0.00

    23 27 0.00

    24 27

    0.00

    25 27 0.00

    26 27 0.00

    27 27 100.00

    As you can see the numerator & denominator are correct but the divison results in zero results until we get to 1/1 = 100??

    still lost....but a little closer

  • February 10, 2009 at 5:01 am

    #941091

    OK

    got that sorted...the over clause shouldnt have a comma after the partition statement

    next some issues with parenthesis

    the final & correct sql statement was....

    SELECT [Actual Time], [Handicap Time], [Watch Time], raceid,

    (row_number() over(partition by RaceID order by [Actual Time])

    )as RacePlace,

    (count([Actual Time]) over(partition by RaceID)

    ) as raceCount,

    CAST(

    1.*(row_number() over(partition by RaceID order by [Actual Time])

    )

    /

    ((count([Actual Time]) over(partition by RaceID)

    ))*100 as decimal(5,2)

    ) as PercPlace

    FROM vwResultsActualTime

    my problem now however is that i wish to add a column to the tavble so i can forget this horrid construct!!

    i know i need to join the two statements somehow..... but how?

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy