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