[SQL] Was: fetch first rows of grouped data

[Guy Fraser]

        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 = '{}'
);




---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings