From: Frank Bax <fbax@sympatico.ca>
To: pgsql-sql@postgresql.org
Subject: Re: Displaying first, last, count columns
Date: Wed, 21 Jun 2006 11:21:36 -0400
Message-ID: <5.2.1.1.0.20060621111608.00abcdb0@pop6.sympatico.ca> (raw)
In-Reply-To: <ce4072df0606210755j40941cf7v44648f2a0e69be56@mail.gmail.co m>
At 10:55 AM 6/21/06, Worky Workerson wrote:
>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?
Is this what you're looking for?
SELECT min(time_occurred) AS first_seen, max(time_occurred) AS last_seen,
count(*), prog_data from t group by prog_data;
Since this query has no WHERE or HAVING clause, this query will read the
entire table. There is nothing you can do to speed it up. If you have
enough RAM to hold the entire table (and appropriate setting to utilize
it), then a second (and subsequent) run of the query will be faster than
the first, but that's as good as it gets.
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: fbax@sympatico.ca
Subject: Re: Displaying first, last, count columns
In-Reply-To: <5.2.1.1.0.20060621111608.00abcdb0@pop6.sympatico.ca>
* 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