Hi all. I hope this makes sense... :-)
I have a table with a list of drivers (who take part in karting events).
i also have a table called events (whichin this table we store the driverid, eventype,finishingposition
and other details which for now is not important)
What i want to do is firstly
find out the top 10 drivers based on
total number of events they have taken part in
total number of event wins (where finishingposition = 1)
I've been playing around with possible ways to do this - but i think my way is rubbish to be honest (im not the greatest exponent of sql in all honesty)
the way i've tried doing it is as follows
i created a view called qrytotalraces and rank
SELECT TOP (100) PERCENT Driver, DriverID,
(SELECT COUNT(*) AS Expr1
FROM dbo.qryEventDetails AS t2
WHERE (t1.DriverID = DriverID) AND (EventTypeID = 7)) AS TotalRaces,ROW_NUMBER() OVER (ORDER BY (SELECT COUNT(*) AS Expr1
FROM dbo.qryEventDetails AS t2
WHERE (t1.DriverID = DriverID) AND (EventTypeID = 7)) DESC) AS Ranking
FROM dbo.TblDriver AS t1
ORDER BY TotalRaces DESC
Then on my online app i can find a driver by driverid and see their ranking.
the problem is
1. i think there must be a better more effective way to do ranking of results
2. i will need to actually do a few more queries like this to get ranks based on other criteria
i.e - total wins, wins/races percentage, total podiums, total poles.
so i would in fact need 4 more queries like the above
is it possible to combine all this into 1 effective query
or if i had to use 5 queries in total to get the results i wanted (and display them in a grid)
what type of overhead would that be
bearing in mind i have over 4,000 drivers across a daily average of 20 events taking place.
Thanks guys/gals
by the way im using sql 2008
i did notice when i created my query i got the error
"SQL text cannot be represented in the grid pane and diagram pane."
i assume this is okay - given that the results are still displayed okay? (dont really need to see anything graphically)
