for a MySQL example reference -----Check the front of each course2 results
CREATE TABLE Studentgrade (
Stuid CHAR (4),--School number
SubId INT,--Course number
Grade INT,--Score
PRIMARY KEY (stuid,subid)
)
GO
--The data in the table is as follows
INSERT into Studentgrade (stuid,subid,grade) VALUES (' 001 ', 1,97);
INSERT into Studentgrade (stuid,subid,grade) VALUES (' 001 ', 2,50);
INSERT into Studentgrade (stuid,subid,grade) VALUES (' 001 ', 3,70);
INSERT into Studentgrade (stuid,subid,grade) VALUES (' 002 ', 1,92);
INSERT into Studentgrade (stuid,subid,grade) VALUES (' 002 ', 2,80);
INSERT into Studentgrade (stuid,subid,grade) VALUES (' 002 ', 3,30);
INSERT into Studentgrade (stuid,subid,grade) VALUES (' 003 ', 1,93);
INSERT into Studentgrade (stuid,subid,grade) VALUES (' 003 ', 2,95);
INSERT into Studentgrade (stuid,subid,grade) VALUES (' 003 ', 3,85);
INSERT into Studentgrade (stuid,subid,grade) VALUES (' 004 ', 1,73);
INSERT into Studentgrade (stuid,subid,grade) VALUES (' 004 ', 2,78);
INSERT into Studentgrade (stuid,subid,grade) VALUES (' 004 ', 3,87);
GO
--View data
SELECT * FROM Studentgrade
If there is a tie, only two students will be taken.
--Method One:
SELECT DISTINCT *
From Studentgrade as T1
where Stuid in
(SELECT top 2 stuid
From Studentgrade as T2
where T1.subid=t2.subid
ORDER BY T2.grade Desc)
Order by SubID, Grade desc
--Method Two:
SELECT * from Studentgrade a WHERE (select COUNT (1) from Studentgrade where Subid=a.subid and GR Ade> =a.grade) <=2
--Method Three:
SELECT * FROM Studentgrade t
Where (select COUNT (1) from Studentgrade where Subid=t.subid and grade> T.grade) <=1
ORDER BY Subid,grade Desc
-Results
drop table Studentgrade
SQL statement with top N ranked in each group after grouping