Message-ID: <4A7ADDCA.2060809@beecom.ch>
Date: Thu, 06 Aug 2009 15:42:34 +0200
From: Leo Mannhart <leo.mannhart@beecom.ch>
User-Agent: Thunderbird 2.0.0.22 (X11/20090608)
MIME-Version: 1.0
To: John <jfabiani@yolo.com>
CC: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
Subject: Re: two records per row from query
References: <200908051651.13235.jfabiani@yolo.com>
In-Reply-To: <200908051651.13235.jfabiani@yolo.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

John wrote:
> 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 have a list of classes that is perfect for our needs.  However, I need to 
> create the second table (from a query) to feed to a report writer so it can 
> write out a single line of text for two records.
> Like:
> 
> Your class dates are as follows
> 
>    Date              Date
> 01/01/2009           01/02/2009
> 01/08/2009           01/10/2009
> 03/31/2009           04/05/2009
> and will continue until the all the classes are printed.
> 
> The problem of course is the table has a row per class and the report writer 
> needs two class dates per row.
> 
> I have no idea how to do this using SQL.
> 
> Thanks in advance,
> Johnf
> 
Can you give a more precise example please? I don't get what you really
need. What I understand is that you want 1 record back for each
sessionid with the earliest and latest class_date.

I've done the following:

lem=# select * from mytable;
 pkid |     class_date      | sessionid
------+---------------------+-----------
    1 | 2009-01-01 00:00:00 |      2101
    2 | 2009-01-02 00:00:00 |      2101
    3 | 2009-01-01 00:00:00 |      2102
    4 | 2009-01-02 00:00:00 |      2102
    5 | 2009-01-01 00:00:00 |      2103
    6 | 2009-01-02 00:00:00 |      2103
    7 | 2009-01-03 00:00:00 |      2103
(7 rows)

and then:

lem=# select min(pkid) as pkid
lem-#       ,min(class_date) as class_date1
lem-#       ,max(class_date) as class_date2
lem-#       ,sessionid
lem-# from   mytable
lem-# group by sessionid;
 pkid |     class_date1     |     class_date2     | sessionid
------+---------------------+---------------------+-----------
    5 | 2009-01-01 00:00:00 | 2009-01-03 00:00:00 |      2103
    3 | 2009-01-01 00:00:00 | 2009-01-02 00:00:00 |      2102
    1 | 2009-01-01 00:00:00 | 2009-01-02 00:00:00 |      2101
(3 rows)

Is this what you need or is there something else? Can you give more
sample data and the result you expect from it?


Cheers, Leo