Displaying first, last, count columns

From: "Worky Workerson" <worky(dot)workerson(at)gmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Displaying first, last, count columns
Date: 2006-06-21 14:55:54
Message-ID: ce4072df0606210755j40941cf7v44648f2a0e69be56@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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!

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message virgi 2006-06-21 15:06:12 How to get a result in one row
Previous Message Hélder M. Vieira 2006-06-20 21:54:35 Re: [SQL] Problema com função UPPER