From: A. Kretschmer <andreas.kretschmer@schollglas.com>
To: pgsql-sql@postgresql.org
Subject: Re: two records per row from query
Date: Thu, 6 Aug 2009 07:21:08 +0200
Message-ID: <20090806052108.GA22410@a-kretschmer.de> (raw)
In-Reply-To: <200908051651.13235.jfabiani@yolo.com>
References: <200908051651.13235.jfabiani@yolo.com>
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
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
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: andreas.kretschmer@schollglas.com
Subject: Re: two records per row from query
In-Reply-To: <20090806052108.GA22410@a-kretschmer.de>
* 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