[SQL] Grouping Too Closely

[Thomas F. O'Connell]

I have a table that looks like this: CREATE TABLE my_table (     pkey serial PRIMARY KEY,     fkey int NOT NULL REFERENCES my_other_table( pkey ),     uid int NOT NULL REFERENCES user( pkey ),     seq1 int,     seq2 int ); Basically, for each fkey that exists in my_table, there is a sequence represented by seq1, which covers every record corresponding to a given fkey. Then there is a subset of records covered by seq2, which increments over the course of a given fkey, but might span multiple records. E.g., pkey | fkey | uid | seq1 | seq2 --------------------------------------- 1        | 1    | 1    | 1        | 1 2        | 1    | 2    | 2        | 1 ... What I'd like to be able to do is select all records corresponding to the minimum value of seq1 for each value of seq2 corresponding to a given fkey (with a lower bound on the value of seq2). My first attempt looked like this: SELECT fkey, uid, seq2 FROM my_table WHERE seq2 > 2 GROUP BY fkey, seq2, uid, seq1 HAVING seq1  = min( seq1 ) but this groups too closely to return the desired results. My next attempt looked like this (where I use the shorthand for min in the subquery): SELECT fkey, uid, seq2 FROM my_table AS mt1 WHERE mt1.seq2 > 2 AND ( mt1.uid, hh1.seq1 ) IN (         SELECT mt2.player_id, mt2.order_no         FROM my_table AS mt2         WHERE mt2.fkey = mt1.fkey         AND mt2.seq2 = mt1.seq2         GROUP BY mt2.seq1, mt2.uid         ORDER BY mt2.seq1 ASC         LIMIT 1 ) GROUP BY mt1.holdem_game_id, mt1.holdem_round_type_id, mt1.player_id This seems like it works, but it is abominably slow, running on the order of days across 1.5 million rows rather than the seconds (or preferably milliseconds) I'd prefer. I have this annoying feeling that I'm overlooking a reasonably efficient in-between query. -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i™ http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005