Re: Pet Peeves?

On Sat, 2009-01-31 at 15:54 -0800, Octavio Alvarez wrote:
> On Sat, 2009-01-31 at 23:36 +0000, Gregory Stark wrote:
> > Octavio Alvarez <alvarezp@alvarezp.ods.org> writes:
> >
> > What about a WHERE clause like
> >
> > WHERE P1 > P2
>
> You could either:
>
> (1) do "FROM grades AS g1 INNER JOIN grades AS g2 ON g1.P1 > g2.P2",
> generating the record set before applying the crosstab transformation.

Just to remove all the stupid things I said about the first solution to the WHERE P1 > P2 problem:

Your grades table would be defined as:

test=# \d grades
         Table "public.grades"
 Column |       Type        | Modifiers
--------+-------------------+-----------
 st     | character varying |
 su     | character varying |
 p      | bigint            |
 gr     | bigint            |
Indexes:
    "grades_st_key" UNIQUE, btree (st, p, su)

st = student; su = subject; p = period; gr = grade

The non-crosstab query that gives you the recordset for the crosstab, would be:

SELECT p2_gt_p1.st, p2_gt_p1.su, grades.p, grades.gr
FROM (
   SELECT g1.st, g1.su, g1.p, g1.gr, g2.p, g2.gr
   FROM grades g1 INNER JOIN grades g2
      ON g1.st = g2.st
         AND g1.su = g2.su AND g2.p = 2
         AND g1.p = 1 AND g2.gr > g1.gr
   ) AS p2_gt_p1
   LEFT JOIN grades USING (st, su);