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
Message-ID: <200908060648.23486.jfabiani@yolo.com> (raw)
In-Reply-To: <20090806052108.GA22410@a-kretschmer.de>
References: <200908051651.13235.jfabiani@yolo.com>
<20090806052108.GA22410@a-kretschmer.de>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jfabiani@yolo.com
Subject: Re: two records per row from query
In-Reply-To: <200908060648.23486.jfabiani@yolo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox