How to write a group rank in a step

1. For example, there are two classes, four people, 2 students per class, 2 genius

2. Use the Row_number () function to rank two classes, such as

3. Find out the top three students of each class, just use the rank, observed rank is everyone in their class position, so in the outside package a layer in the condition of query on it can be

Attach the SQL statement:

 withCTE as(SELECT 'Class One'Class'Zhang San'Name AAchievementsUNION  All         SELECT 'Class One'Class'Zhang Si'Name -AchievementsUNION  All         SELECT 'Class Two'Class'Zhang Five'Name -AchievementsUNION  All         SELECT 'Class Two'Class'Zhang Six'Name98AchievementsUNION  All         SELECT 'Class Two'Class'Zhang Qi'Name $AchievementsUNION  All         SELECT 'Class One'Class'Zhang Eight'Name theAchievementsUNION  All         SELECT 'Class Two'Class'Nines'Name theAchievementsUNION  All         SELECT 'Class One'Class'Zhang 10'Name theresults)--SELECT * from CTE order by [class] descSelectA.*  from (SelectRow_number () Over(PARTITION by [class] Order  by [Achievements] desc) Rank,*  fromCTE) AwhereA.rank<=3 Order  byClassdesc, rankASC

How to write a group rank in a step