How to find the second best grade of a student. Using Postgresql user-defined aggregate

You can define your own aggregate function in Postgres, let's say you want to get only two array elements from a set of rows, you can use this (this violates DestroyCity principle though, but we are constrained by the fact that user-defined-aggregates accepts function with two parameters only, so there's nothing we can do about it):

create aggregate two_elements(anyelement)
(
sfunc = array_limit_two,
stype = anyarray,
initcond = '{}'
);

create or replace function array_limit_two(anyarray, anyelement) returns anyarray
as 
$$
begin
 if array_upper($1,1) = 2 then
  return $1;
 else
  return array_append($1, $2);
 end if;
end;
$$ language 'plpgsql';

Get the data here: How to find the second best grade of a student?

Then this...

select student, two_elements(grade order by grade desc)
from 
student_grades
group by student;

...will return(two best grades of a student):

student | two_elements 
---------+--------------
 george  | {40}
 john    | {100,90}
 paul    | {50,30}

To return the second best grades only, use this:

select student, array_min( two_elements(grade order by grade desc) )
from 
student_grades
group by student;

Here's the array_min function:

create or replace function array_min(anyarray) returns anyelement
as
$$
select min(unnested) from( select unnest($1) unnested ) as x
$$ language sql;

 student | array_min 
---------+-----------
 george  |        40
 john    |        90
 paul    |        30
(3 rows)

I think this code is more performant than the previous one


Wisdom of the day:

Unless you actually are going to solve the general problem, don't try and put in place a framework for solving a specific one, because you don't know what that framework should look like.

Wisdom source: http://tirania.org/blog/archive/2003/Sep-29.html