On Mon, 26 Jan 2009, "Marc Mamin" <M.Mamin@intershop.de> writes:
> create table test
> (
> time int8, --store the time as epoch
> a_group varchar,
> category varchar
> )
>
> ...
>
> SELECT
> FIRST.a_group,
> FIRST.time as first_time,
> FIRST.category as first_category,
> LAST.time as last_time,
> LAST.category as last_category,
> AGG.c_count,
> AGG.c_all
> FROM
> ...
I think the problem in here is that you want to collect the first and
last values in the same row. Instead, splitting them into two sequential
rows would suit better to your database schema design, and you can
rebuild the data structure as you want in the application tier
later. For instance, consider below example:
test=# SELECT ts, grp, val FROM foo;ts | grp | val
----+-----+----- 1 | 1 | 1 2 | 1 | 2 3 | 1 | 3 4 | 2 | 1 4 | 2 | 2 5 | 3 | 1
(6 rows)
test=# SELECT foo.ts, foo.grp, foo.val FROM (SELECT grp, MAX(ts) AS max_ts, MIN(ts) AS min_ts
FROMfoo GROUP BY grp) AS bar INNER JOIN foo ON foo.grp = bar.grp AND (foo.ts =
bar.min_tsOR foo.ts = bar.max_ts);ts | grp | val
----+-----+----- 1 | 1 | 1 3 | 1 | 3 4 | 2 | 1 4 | 2 | 2 5 | 3 | 1
(5 rows)
After receiving above output, you can traverse returned rows one by one
in the application layer and output desired results.
Regards.
