CREATE TABLE Students (course varchar), Stu_name varchar (TEN), City varchar (ten), score int INSERT into students values (' math ', ' Jack ', ' Tianjin ', ' n '), insert into students values (' math ', ' Jordan ', ' Tianjin ', 80); INSERT into students values (' math ', ' James ', ' Beijing ', ' n '), insert into students values (' English ', ' Jack ', ' Tianjin ', ' n '); insert into students values (' English ', ' Jordan ', ' Tianjin ', max), insert into students values (' English ', ' James ', ' Beijing ', ' n '); INSERT into Students values (' language ', ' Jack ', ' Tianjin ', ' Max '), insert into students values (' language ', ' Tom ', ' Tianjin ', ' n '); INSERT INTO students VALUES (' Language ', ' Jordan ', ' Tianjin ', ' Studentsselect ');D elete from the students--when the maximum is multiple, take a SELECT * FROM (Selec T Course,stu_name,city,score,row_number () over (partition by course ORDER BY score Desc) as RN from students ) A WHERE A.rn <=1; --Maximum is more than one time to take select--a.course,a.score,b.stu_name,b.city,b.course course1,b.score score1 b.*from (select Course,max ( Score) score from students GRoup by course) Aleft joinstudents b ona.course=b.course and A.score=b.score;
Query maximum All column information after SQL grouping