create table student_grades
(
student varchar(100),
grade int
);
insert into student_grades values
('john',70),
('john',80),
('john',90),
('john',100);
insert into student_grades values
('paul',20),
('paul',10),
('paul',50),
('paul',30);
insert into student_grades values
('george',40);
Desired output:
student | grade ---------+------- george | 40 john | 90 paul | 30 (3 rows)
Here's the code:
with ranking as ( select student, grade, rank() over(partition by student order by grade DESC) as place from student_grades ) select student, grade from ranking x where exists ( select null from ranking where place <= 2 and student = x.student group by student having max(place) = x.place )
The following is shorter(works on Postgresql, Oracle) and simpler to read than above:
with ranking as ( select student, grade, rank() over(partition by student order by grade DESC) as place from student_grades ) select student, grade from ranking where (student, place) in ( select student, max(place) from ranking where place <= 2 group by student )
If only Postgres allows LIMIT clause inside an aggregation and made array_max a built-in function, this could be the shortest code:
select student, array_max( array_agg(grade order by grade desc limit 2) ) from student_grades group by student;
No comments:
Post a Comment