Sunday, February 6, 2011

How to find the second best grade of a student?

How to find the second best grade of the student? (If there's no second grade, just return the first one)

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