Re: Pet Peeves?

From: Octavio Alvarez <alvarezp(at)alvarezp(dot)ods(dot)org>
To: Gregory Stark <stark(at)enterprisedb(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Pet Peeves?
Date: 2009-02-01 19:41:25
Message-ID: 1233517285.5647.83.camel@localhost.localdomain
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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);

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2009-02-01 19:52:56 Re: urgent request : PSQLException: FATAL: could not open relation XXX: No such file or directory
Previous Message Mohamed 2009-02-01 19:11:53 Re: Indices types, what to use. Btree, Hash, Gin or Gist