Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-general by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group