Help with ranking results of a query

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

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

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:

strange i get alot of syntax errors when trying the above :-(

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

                                              

1:
2:
3:
4:
5:
6:
7:
8:

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.

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]

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?

provide the sample data from your Events table for quick help.

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

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:

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 

• Driver | DriverID | RaceRank | TotalRaces| 

I understood what you want.

As I asked before, is it possible to provide some sample data?

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?

http://www.yousendit.com/download/dVlyeEVUaytIcWRMWEE9PQ

here is a link to the database scripts

Thanks

hope it helps

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)

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:

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
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:

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