Was: fetch first rows of grouped data - Mailing list pgsql-sql

Hi there              I have a list of events that take place in a certain city at a       certain date. Now I
wouldlike 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 :

SELECTct_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
GROUPBY  ct_city ) AS data_set 
 
WHERE ct_city = city ANDct_id IN (match[0],match[1])
ORDER BYct_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 = '{}'
);