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