Select query for top 3 batting and bowling rank in cric

  • 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

  • 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
  • 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

  • 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
  • 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

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

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

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

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