?
Solved

Help with ranking results of a query

Posted on 2009-04-28
19
Medium Priority
?
685 Views
Last Modified: 2012-08-14
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)

0
Comment
Question by:MiamiDolphins
  • 9
  • 7
  • 3
19 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24252641
Well if you created a view that does top 100 percent with an order in a view, it won't work.  Here's an article I wrote with details on why:

http://sqlservernation.com/blogs/brandongalderisi/archive/2009/03/15/when-ordering-in-a-view-doesn-t-work.aspx


If you need to do ranking, then you probably should look at the build in RANK() and ROW_NUMBER() functions.
0
 

Author Comment

by:MiamiDolphins
ID: 24252970
the problem is
if i use identity in my sql

and then on my actual web application

when i try and filter to show 1 record for example
the indentity will be 1 and not the actual rank of the driver?

my query is actually using row_number but maybe iim not doing it right which is why i was after some help
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24253261
Try:


select driver,driverid,cnt,row_number() over (order by cnt desc) as Ranking
from
(SELECT Driver, DriverID,
count(t2.*) cnt,
FROM          dbo.qryEventDetails AS t2
  left join dbo.qryEventDetails AS t2
    on (t1.DriverID = t2.DriverID) AND (t2.EventTypeID = 7)
FROM         dbo.TblDriver AS t1
)a

Open in new window

0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:MiamiDolphins
ID: 24253815
strange i get alot of syntax errors when trying the above :-(
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24254053
Yeah... something went HORRIBLY wrong on the copy paste.

Try this
select driver,driverid,cnt,row_number() over (order by cnt desc) as Ranking
from
(SELECT Driver, DriverID,
count(t2.driverid) cnt
FROM   dbo.TblDriver AS t1
  left join dbo.qryEventDetails AS t2
    on (t1.DriverID = t2.DriverID) AND (t2.EventTypeID = 7)
)a

Open in new window

0
 

Author Comment

by:MiamiDolphins
ID: 24255843
still cant get that to work mate

i get the following error now
Column 'dbo.TblDriver.Driver' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.


0
 

Author Comment

by:MiamiDolphins
ID: 24255940
how about this query?

   SELECT
    RaceRank,
   Rankings.Driver,
                Rankings.DriverID,
           TotalRaces /* fk */
         
      FROM
      (            SELECT
      qryTotalRaces.Driver,
                  qryTotalraces.DriverID,
                 TotalRaces,
                       
                  ROW_NUMBER() OVER
                  (
                        ORDER BY
                             TotalRaces DESC
                  ) AS RaceRank
            FROM
                 qryTotalRaces
                  INNER JOIN TblDriver ON qryTotalRaces.DriverID = TblDriver.DriverID
           
      ) AS [Rankings]
0
 

Author Comment

by:MiamiDolphins
ID: 24255947
If i join the other queries (once i make them) how much of a performance hit is that and is this the best way to reallly do it?

0
 
LVL 41

Expert Comment

by:Sharath
ID: 24255981
provide the sample data from your Events table for quick help.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 24256040
did you try these queries? you have asked two questions. How do you want the result? In one result set or two different result sets?
If one, provide your expected output. Also i asked, provide sample input if you are not getting what you are expected witht the given queries.

select TOP 10 DriverId,COUNT(*) as Cnt 
  from [Events] 
 group by DriverId 
 order by COUNT(*) DESC
 
select TOP 10 DriverId,COUNT(*) as Cnt 
  from [Events] 
 where finishingposition = 1
 group by DriverId 
 order by COUNT(*) DESC

Open in new window

0
 

Author Comment

by:MiamiDolphins
ID: 24256120
The queries are working fine - im getting the results i expected.

i'm just concerned about the speed and overall performance of the queries and the fact my expected results i want is

  • Driver Name | Races | RaceRank | Wins | WinRank | Win%| Win%Rank | Poles| PoleRank
The above is what i want in a ideal world.


i thought that in order to accomplish this i would need to create 5 queries - similar to the one above and join them

i was thinking that if i do this the performance hit would be large so was wondering if there is a better way to do it in 1 query without needing so many different subqueries/views

doing this
...

   SELECT
    RaceRank,
   Rankings.Driver,
                Rankings.DriverID,
           TotalRaces /* fk */
         
      FROM
      (            SELECT
      qryTotalRaces.Driver,
                  qryTotalraces.DriverID,
                 TotalRaces,
                       
                  ROW_NUMBER() OVER
                  (
                        ORDER BY
                             TotalRaces DESC
                  ) AS RaceRank
            FROM
                 qryTotalRaces
                  INNER JOIN TblDriver ON qryTotalRaces.DriverID = TblDriver.DriverID
           
      ) AS [Rankings]

gives me the resutls i want if i wanted

  • Driver | DriverID | RaceRank | TotalRaces|
but there are a few more elements that i would like to produce (as it will hopefully all be displayed in a grid in a asp.net form) such as

TotalWins | Win/Race% | Poles | Podium Finishes|
for each of these i would also like to show the drivers related rank within each.

I hope that makes sense


definitions

TotalWins = Where EventDetails.FinishingPosition = 1 (means they won the race as they finished 1st)

Poles = Where EventDetails.EventTypeID = 5 (says that the event was a qualification event) and FinishingPosition = 1 (means they are on pole)

Podium  = Whhere EventDetails.EventtypeID = 5 AND FinishingPosition = 1,2,3


Doing the queries to produce the results isnt really a issue - the issue really is combining it all into 1 view
0
 
LVL 41

Expert Comment

by:Sharath
ID: 24257033
I understood what you want.
As I asked before, is it possible to provide some sample data?
0
 

Author Comment

by:MiamiDolphins
ID: 24258526
yeah sure - how should i supply it ?
im only using sql express so cant export data :-(

update

i've used sql packager to create the database and script

do you have an email address for me to send it to? or should i just attach it here?
0
 

Author Comment

by:MiamiDolphins
ID: 24258558
http://www.yousendit.com/download/dVlyeEVUaytIcWRMWEE9PQ

here is a link to the database scripts

Thanks

hope it helps
0
 
LVL 41

Expert Comment

by:Sharath
ID: 24262474
There are lot of gaps between your query posted here and the DatabaseCreator.sql (from the shared link).
I didn't find qryTotalRaces table in DatabaseCreator.sql
There is no FinishingPosition column in TblEventDetails table. I assumed that it is ClassPosition.
I didn't find EventtypeID column in any of the table. I assume that it is EventType column in tblEvent table. Observe the LEFT JOIN in my query.
I used DENSE_RANK over ROW_NUMBER. DENSE_RANK will assign same rank if two or more people have same rank (got same number of wins or pole etc).
You can replace it ROW_NUMBER if you want.

select Driver,
       Races,dense_rank() over (order by Races desc) RaceRank,
       TotalWins,dense_rank() over (order by TotalWins desc) WinRank,
       Win_Percentage,dense_rank() over (order by Win_Percentage desc) Win_Percentage_Rank,
       Poles,dense_rank() over (order by Poles desc) PoleRank
  from (select DriverId,
               COUNT(*) Races,
               SUM(case when ED.ClassPosition = 1 then 1 else 0 end) TotalWins,
               case (SUM(case when ED.ClassPosition = 1 then 1 else 0 end)) when 0 then 0
               else COUNT(*)*100.0/(SUM(case when ED.ClassPosition = 1 then 1 else 0 end)) end Win_Percentage,
               SUM(case when E.EventType = 5 and  ED.ClassPosition = 1 then 1 else 0 end) Poles
          from TblEventDetails ED
          left join tblEvent E on ED.EventId = E.EventId
         group by DriverId) t1
  join tblDriver t2 on t1.DriverId = t2.DriverId
  order by ROW_NUMBER() over (order by Races desc)

Open in new window

0
 
LVL 41

Expert Comment

by:Sharath
ID: 24262521

If you are interested in only TOP 10 records according to RaceRank, or PoleRank or WinRank, or Win_Percentage_Rank, then try like this.
replace the column in WHERE and ORDER BY clauses with whatever you want.
select *
  from (select Driver,
               Races,row_number() over (order by Races desc) RaceRank,
               TotalWins,row_number() over (order by TotalWins desc) WinRank,
               Win_Percentage,row_number() over (order by Win_Percentage desc) Win_Percentage_Rank,
               Poles,row_number() over (order by Poles desc) PoleRank
          from (select DriverId,
                       COUNT(*) Races,
                       SUM(case when ED.ClassPosition = 1 then 1 else 0 end) TotalWins,
                       case (SUM(case when ED.ClassPosition = 1 then 1 else 0 end)) when 0 then 0
                       else COUNT(*)*100.0/(SUM(case when ED.ClassPosition = 1 then 1 else 0 end)) end Win_Percentage,
                       SUM(case when E.EventType = 5 and  ED.ClassPosition = 1 then 1 else 0 end) Poles
                  from TblEventDetails ED
                  left join tblEvent E on ED.EventId = E.EventId
                 group by DriverId) t1
          join tblDriver t2 on t1.DriverId = t2.DriverId) t3
   where RaceRank < 10
   order by RaceRank desc

Open in new window

0
 
LVL 41

Accepted Solution

by:
Sharath earned 2000 total points
ID: 24262548
My bad. made a mistake in winning percentage calculation. check this.
select *
  from (select Driver,
               Races,row_number() over (order by Races desc) RaceRank,
               TotalWins,row_number() over (order by TotalWins desc) WinRank,
               Win_Percentage,row_number() over (order by Win_Percentage desc) Win_Percentage_Rank,
               Poles,row_number() over (order by Poles desc) PoleRank
          from (select DriverId,
                       COUNT(*) Races,
                       SUM(case when ED.ClassPosition = 1 then 1 else 0 end) TotalWins,
                       case COUNT(*) when 0 then 0
                       else (SUM(case when ED.ClassPosition = 1 then 1 else 0 end))*100.0/COUNT(*) end Win_Percentage,
                       SUM(case when E.EventType = 5 and  ED.ClassPosition = 1 then 1 else 0 end) Poles
                  from TblEventDetails ED
                  left join tblEvent E on ED.EventId = E.EventId
                 group by DriverId) t1
          join tblDriver t2 on t1.DriverId = t2.DriverId) t3
   where RaceRank < 10
   order by RaceRank 
   

Open in new window

0
 

Author Comment

by:MiamiDolphins
ID: 24277339
Excellent Sharath


just want i wanted you really are a star


yeah sorry about the gaps - i made changes since i posted my original question

Thanks

0
 
LVL 41

Expert Comment

by:Sharath
ID: 24277434
Glad i could help.
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question