Select query for top 3 batting and bowling rank in cric

Question

Select query for top 3 batting and bowling rank in cric

muthukrishnan.e

SSC Enthusiast

Points: 154
More actions
January 13, 2008 at 10:36 pm

#98780

The table is like
id - level - runs - sixes - fours - wickets - inruns
1 1 40 4 3 5 60
2 1 45 3 3 5 61
3 1 50 3 4 5 60
4 1 40 2 2 2 40
5 1 30 1 1 6 40
I want to select top three wicket takers with respect to inruns ?
RANK - ID - WICKETS - INRUNS
1 - 5 - 6 - 40
2 - 1 - 5 - 60
2 - 3 - 5 - 60
4 - 2 - 5 - 61
I want the above results..... to get this results i need a select query
anyone who knows help me ....
with regards krishna
muthukrishnan.e@lycos.com

Viewing 7 posts - 1 through 6 (of 6 total)

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

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

Look up the DENSE_Rank funcion in books online. It's a feature new in sQL 2005 and does exactly what you want.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

muthukrishnan.e SSC Enthusiast Points: 154 More actions · Answer 2

hi,

i dont want to skip ranks .... i want it like 1, 2, 2, 4, 5, 5, 7......

i want to use RANK() and get the results

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 3

muthukrishnan.e (1/14/2008)
hi,
i dont want to skip ranks .... i want it like 1, 2, 2, 4, 5, 5, 7......

Ok. I said dense_rank based on the rank values you had originally in your post. The edited version requires rank, not dense_rank

i want to use RANK() and get the results

Rank will get you the ranking that you need, and there are some examples of its use in BoL. From what I can see of the problem, Rank order by wickets desc, inruns should get you the rankings that you want.

So, something like this should work.

Select ... FROM (

Select ..., Rank () OVER(order by wickets desc, inruns) AS Ranking

FROM tbl ) RankedSubQuery

Where Ranking <4

One thing. you said you wanted the top 3, but your example showed the top 4. Which one's right?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ivanna Noh SSCarpal Tunnel Points: 4138 More actions · Answer 4

try this:

select top 4 [rank]= Rank () OVER(order by wickets desc, inruns),id,wickets,inruns from **tablename** order by wickets desc, inruns

you could also try the "WITH TIES" option when selecting TOP n rows so that tied values are included as extra rows...BOL has details

muthukrishnan.e SSC Enthusiast Points: 154 More actions · Answer 5

It is for top 3 .... for example of ties i've given one more row..... from that i want top three...

muthukrishnan.e SSC Enthusiast Points: 154 More actions · Answer 6

Thank you very much for both of you ... both the queries are working fine