X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 384619FA501 for ; Wed, 21 Jun 2006 12:10:58 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 06043-01 for ; Wed, 21 Jun 2006 12:10:52 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from wx-out-0102.google.com (wx-out-0102.google.com [66.249.82.207]) by postgresql.org (Postfix) with ESMTP id 3EC419FA19B for ; Wed, 21 Jun 2006 12:10:50 -0300 (ADT) Received: by wx-out-0102.google.com with SMTP id s13so134100wxc for ; Wed, 21 Jun 2006 08:10:50 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=pNLdgjsUFCWXiPBVNLiCUcgb5yMPSh3HG5sBFlDOHrFsGL9A5BaSq3NR6RNmC2QEOEknG6aDHSo3bNqAVL6uRFhYUrPkacRrVXNEO20LA6O11LWR951zyRZrfXFcnmKHF9o/ti491hfU8fGKyLY4/Z5ezMxqB2thCJDmZVFri1c= Received: by 10.70.71.4 with SMTP id t4mr1257945wxa; Wed, 21 Jun 2006 08:10:49 -0700 (PDT) Received: by 10.70.66.15 with HTTP; Wed, 21 Jun 2006 08:10:48 -0700 (PDT) Message-ID: Date: Wed, 21 Jun 2006 10:10:48 -0500 From: "Aaron Bono" To: "Worky Workerson" Subject: Re: Displaying first, last, count columns Cc: pgsql-sql@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_2083_6694939.1150902648435" References: X-Google-Sender-Auth: 7b9f100d7536d9c5 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/236 X-Sequence-Number: 25472 ------=_Part_2083_6694939.1150902648435 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline I would suggest: select max(time_occurred) AS last_seen, min(time_occurred) AS first_seen, count(*), prog_data from t group by prog_data I would also suggest you use inner joins rather than put all your tables in the from and join in the where clause. It is much easier to read and understand what you are trying to do. The query you have is not exactly the same as what I put above but I bet the performance is bad because you have inner queries that have constraints based on the outer query. I usually avoid this as much as possible. -Aaron Bono On 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? > > Thanks! ------=_Part_2083_6694939.1150902648435 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline I would suggest:

select
max(time_occurred) AS last_seen,
min(time_occurred) AS first_seen,
count(*),
prog_data
from t
group by
prog_data

I would also suggest you use inner joins rather than put all your tables in the from and join in the where clause.  It is much easier to read and understand what you are trying to do.  The query you have is not exactly the same as what I put above but I bet the performance is bad because you have inner queries that have constraints based on the outer query.  I usually avoid this as much as possible.

-Aaron Bono

On 6/21/06, Worky Workerson <worky.workerson@gmail.com> 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?

Thanks!
------=_Part_2083_6694939.1150902648435--