From: John <jfabiani@yolo.com>
To: pgsql-sql@postgresql.org
Subject: Re: two records per row from query
Date: Thu, 6 Aug 2009 06:48:23 -0700
User-Agent: KMail/1.9.9
References: <200908051651.13235.jfabiani@yolo.com>
	<20090806052108.GA22410@a-kretschmer.de>
In-Reply-To: <20090806052108.GA22410@a-kretschmer.de>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200908060648.23486.jfabiani@yolo.com>

On Wednesday 05 August 2009 10:21:08 pm A. Kretschmer wrote:
> In response to John :
> > mytable
> > pkid
> > class_date.
> > sessionid
> >
> > select * from mytable
> > 1 2009/01/01 2101
> > 2 2009/01/02 2101
> >
> > I would like an SQL that would produce
> >
> > newtable
> > pkid,
> > class_date1,
> > class_date2,
> > sessionid1,
> > sessionid2
> >
> > Select * from newtable
> >
> > 1 2009/01/01 2009/01/02 2101 2101
>
> I will try, but i'm not sure if i understand you correctly. Your table
> contains only 2 rows and both rows contains the same sessionid. Can i
> use that sessionid to find the rows that belongs together?
>
> Okay, my table:
>
> test=*# select * from mytable ;
>  pkid | class_date | sessionid
> ------+------------+-----------
>     1 | 2009-01-01 |      2101
>     2 | 2009-01-02 |      2101
>     3 | 2009-02-01 |      2102
>     4 | 2009-02-02 |      2102
>     5 | 2009-03-01 |      2103
>     6 | 2009-03-02 |      2103
> (6 rows)
>
>
> As you can see, there are 3 different sessionid's.
>
> test=*# select distinct on (sessionid1,sessionid2) pkid, classdate1,
> classdate2, sessionid1, sessionid2 from (select least(a.pkid, b.pkid) as
> pkid, least(a.class_date, b.class_date) as classdate1,
> greatest(a.class_date, b.class_date) as classdate2, a.sessionid as
> sessionid1, b.sessionid as sessionid2 from mytable a inner join mytable
> b on (a.sessionid=b.sessionid)) foo order by sessionid1,
> sessionid2,pkid;
>  pkid | classdate1 | classdate2 | sessionid1 | sessionid2
> ------+------------+------------+------------+------------
>     1 | 2009-01-01 | 2009-01-01 |       2101 |       2101
>     3 | 2009-02-01 | 2009-02-01 |       2102 |       2102
>     5 | 2009-03-01 | 2009-03-01 |       2103 |       2103
> (3 rows)
>
>
> Hope that helps...
>
>
> Andreas

Thanks - the sessionid's in fact do match.  It's just that I can have more 
than two (2) classes per sessionid.  So mytable might look like:
 select * from mytable
 1 2009/01/01 2101
 2 2009/01/02 2101
 3 2009/02/05 2101
 4 2009/02/15 2101
 5 2009/02/25 2101

 I will try to use your solution.

I was also looking at using an array aggregate.  I'm not sure how I use it but 
it might work.

Also I'm using 8.3.7 if that helps.

Johnf