From: Leo Mannhart <leo.mannhart@beecom.ch>
To: John <jfabiani@yolo.com>
Cc: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Re: two records per row from query
Date: Thu, 06 Aug 2009 15:42:34 +0200
Message-ID: <4A7ADDCA.2060809@beecom.ch> (raw)
In-Reply-To: <200908051651.13235.jfabiani@yolo.com>
References: <200908051651.13235.jfabiani@yolo.com>
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
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: leo.mannhart@beecom.ch, jfabiani@yolo.com
Subject: Re: two records per row from query
In-Reply-To: <4A7ADDCA.2060809@beecom.ch>
* 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