Subject: Was: fetch first rows of grouped data
From: Guy Fraser <guy@incentre.net>
To: pgsql-sql@postgresql.org
Content-Type: text/plain
Organization: The Internet Centre
Date: Tue, 28 Aug 2007 10:05:03 -0600
Message-Id: <1188317103.8070.34.camel@sigurd.incentre.net>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

        Hi there
        
        I have a list of events that take place in a certain city at a
        certain date. Now I would like to have the first two (ordered by
        date) events for each city.
        Is there a way to do this with one query?
        I am using PostgreSQL 7.4.
        
        Thanks for any tips.
        
        Claudia


I think I may have come up with a possible solution.

Create a selection that produces a unique identifier for each city
ordered by date then use array_accum to collect the unique identifiers 
for each city, then match the first two elements of the array with the 
identifiers.

For instance if you had a table :

CREATE TABLE crazy_talk (
 ct_id bigserial primary key,
 ct_city text,
 ct_date date,
 ct_data text
) ;

Then you could use :

SELECT
 ct_id , 
 ct_city , 
 ct_date , 
 ct_data 
FROM 
 crazy_talk , 
 (SELECT 
  ct_city AS city, 
  array_accum(ct_id) as match 
 FROM 
  crazy_talk 
 ORDER BY 
  ct_city , 
  ct_date 
 GROUP BY 
  ct_city ) AS data_set 
WHERE 
 ct_city = city AND
 ct_id IN (match[0],match[1])
ORDER BY
 ct_city ,
 ct_date
;

I hope this helps, I did not try it, but I think it should work.

PS if you don't have array_accum here it is :

CREATE AGGREGATE array_accum (
    BASETYPE = anyelement,
    SFUNC = array_append,
    STYPE = anyarray,
    INITCOND = '{}'
);