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