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(at)alvarezp(dot)ods(dot)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);