Please give a look at the "ranking" functions such as "row_number()" and "rank()" in books online. Pay particular attention to use of the OVER() clause in conjunction with both the PARTITION BY and ORDER BY clauses.

Kent