From: Worky Workerson <worky.workerson@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Displaying first, last, count columns
Date: Wed, 21 Jun 2006 10:55:54 -0400
Message-ID: <ce4072df0606210755j40941cf7v44648f2a0e69be56@mail.gmail.com> (raw)
I'm having a bit of a brain freeze and can't seem to come up with
decent SQL for the following problem:
I have a table "t" of the form "time_occurred TIMESTAMP, prog_data
VARCHAR" and would like to create a query that outputs something of
the form "first_seen, last_seen, count, prog_data".
I have the current query which gets the first_seen and last_seen via
subqueries, ala
SELECT t1.time_occurred AS first_seen, t2.time_occurred AS last_seen,
t3.count, t1.prog_data
FROM t AS t1, t AS t2
WHERE t1.prog_data = t2.prog_data
AND t1.time_occurred IN (SELECT min(time_occurred) FROM t WHERE
prog_data = t1.prog_data)
AND t2.time_occurred IN (SELECT max(time_occurred) FROM t WHERE
prog_data = t1.prog_data)
but I can't seem to work out how to get the count of all the records
that have. I figure that this is probably a relatively common idiom
... can anyone suggest ways to go about doing this. Also, the
performance of this is pretty horrible, but I figure that creating a
column on t.prog_data should speed things up noticably, right?
Thanks!
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: worky.workerson@gmail.com
Subject: Re: Displaying first, last, count columns
In-Reply-To: <ce4072df0606210755j40941cf7v44648f2a0e69be56@mail.gmail.com>
* 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