Re: two records per row from query

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