January 3, 2009 at 8:07 pm
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
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
Change is inevitable... Change for the better is not.
January 3, 2009 at 9:31 pm
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
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
Change is inevitable... Change for the better is not.
January 3, 2009 at 11:53 pm
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
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
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
Change is inevitable... Change for the better is not.
January 4, 2009 at 2:26 pm
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
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
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
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
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