Using Rank() to get first row based on a filter

In Sql Server we can use Rank() to fetch a row with rank 1 by filtering data on certian criteria.


For ex, using the following table i am fetching highest runs scored by a player in his career.


The following script contains table creation, fetching details for each player with highest runs scored.


Create table tblTest
 (Name varchar(10), Year int,Country varchar(10),RunsScored int, primary key(Name,Year))
insert into tblTest
select 'A',2001,'India',13 union
select 'A',2002,'India',23 union
select 'A',2003,'India',19 union
select 'A',2004,'India',14 union
select 'A',2005,'India',11 union
select 'B',2001,'England',42 union
select 'B',2002,'England',39 union
select 'B',2003,'England',42 union
select 'B',2004,'England',29 union
select 'C',2002,'England',2 union
select 'C',2003,'England',3 union
select 'C',2004,'India',6 union
select 'C',2005,'India',9
select * from
(
select Name, Year, RunsScored, Rank() over (Partition BY Name order by RunsScored DESC, Year DESC) as Rank
from
tblTest
) tmp
where Rank = 1


Results:
======
A 2002 23 1
B 2003 42 1
C 2005  9 1