SQL Implementation Group sorting-instances get the first three students ' names for male and female grades

Table Scores (Name,sex,score), respectively, to obtain the first three names of male and female students.

Create a table:

CREATE TABLE Scores (
Name VARCHAR2 (8), sex varchar2 (1), score number (3,0)
)

Insert Student Scores

INSERT into scores (Name,sex,score) VALUES (' Male a ', ' 1 ', 100);
INSERT into scores (Name,sex,score) values (' Male B ', ' 1 ', 90);
INSERT into scores (Name,sex,score) VALUES (' Male C ', ' 1 ', 80);
INSERT into scores (Name,sex,score) VALUES (' Male d ', ' 1 ', 70);
INSERT into scores (Name,sex,score) values (' Female a ', ' 2 ', 100);
INSERT into scores (Name,sex,score) values (' Female B ', ' 2 ', 90);
INSERT into scores (Name,sex,score) values (' Female C ', ' 2 ', 80);
INSERT into scores (Name,sex,score) values (' Female d ', ' 2 ', 70);

Inquire:

SELECT name, Sex,score
From (SELECT name,
Sex
Score,
Row_number () over (PARTITION by sex ORDER by score DESC) RW
From scores)
WHERE RW < 4

Operation Result:

1 Male a 1 100
2 Male B 1 90
3 Male C 1 80
4 Female a 2 100
5 Female B 2 90
6 Female C 2 80

SQL Implementation Group sorting-instances get the first three students ' names for male and female grades