SQL Server skillfully uses row_number and partition by to group top data

from: http://www.jb51.net/article/29162.htm

--1. Create a test tableCreate Table#score (Namevarchar( -), subjectvarchar( -), scoreint ) --2. Inserting test dataInsert  into#score (Name,subject,score)Values('Zhang San','language',98) Insert  into#score (Name,subject,score)Values('Zhang San','Mathematics', the) Insert  into#score (Name,subject,score)Values('Zhang San','English', -) Insert  into#score (Name,subject,score)Values('John Doe','language', the) Insert  into#score (Name,subject,score)Values('John Doe','Mathematics', the) Insert  into#score (Name,subject,score)Values('John Doe','English', the) Insert  into#score (Name,subject,score)Values('Li Ming','language', -) Insert  into#score (Name,subject,score)Values('Li Ming','Mathematics', the) Insert  into#score (Name,subject,score)Values('Li Ming','English', the) Insert  into#score (Name,subject,score)Values('Linfeng','language', About) Insert  into#score (Name,subject,score)Values('Linfeng','Mathematics', About) Insert  into#score (Name,subject,score)Values('Linfeng','English', -) Insert  into#score (Name,subject,score)Values('Strict','English', the) --3. Take the first 3 data from each disciplineSelect *  from ( SelectSubject,name,score,row_number () Over(PARTITION bySubjectOrder  byScoredesc) asNum from#score) TwhereT.num<= 3 Order  bysubject--4. Delete temporary tablestruncate Table#scoreDrop Table#score

Grammatical form:row_number () over (PARTITION by COL1 ORDER by COL2)
Explanation: Based on the COL1 grouping, the values in the grouping are sorted according to COL2, and the value computed by this function represents the sequential number (the only consecutive unique) result of each set within the group: --------------------------->>>