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 mytable1 2009/01/01 21012 2009/01/02
210132009/02/05 21014 2009/02/15 21015 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
