X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 993BA5280D for ; Thu, 23 Jun 2005 16:16:50 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 12748-09 for ; Thu, 23 Jun 2005 19:16:40 +0000 (GMT) Received: from window.monsterlabs.com (window.monsterlabs.com [216.183.105.176]) by svr1.postgresql.org (Postfix) with SMTP id E565D528C3 for ; Thu, 23 Jun 2005 16:16:39 -0300 (ADT) Received: (qmail 19005 invoked from network); 23 Jun 2005 19:16:39 -0000 Received: from w080.z064003242.bna-tn.dsl.cnc.net (HELO ?192.168.1.22?) (64.3.242.80) by 0 with SMTP; 23 Jun 2005 19:16:39 -0000 Mime-Version: 1.0 (Apple Message framework v730) To: PgSQL - SQL Message-Id: Content-Type: multipart/alternative; boundary=Apple-Mail-62--486924356 From: "Thomas F. O'Connell" Subject: Grouping Too Closely Date: Thu, 23 Jun 2005 14:16:36 -0500 X-Mailer: Apple Mail (2.730) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.025 tagged_above=0 required=5 tests=AWL, HTML_60_70, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200506/282 X-Sequence-Number: 21883 --Apple-Mail-62--486924356 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=WINDOWS-1252; delsp=yes; format=flowed 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 =20= represented by seq1, which covers every record corresponding to a =20 given fkey. Then there is a subset of records covered by seq2, which =20 increments over the course of a given fkey, but might span multiple =20 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 =20= the minimum value of seq1 for each value of seq2 corresponding to a =20 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 =3D 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 =20 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 =3D mt1.fkey AND mt2.seq2 =3D 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 =20 order of days across 1.5 million rows rather than the seconds (or =20 preferably milliseconds) I'd prefer. I have this annoying feeling that I'm overlooking a reasonably =20 efficient in-between query. -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i=99 http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 --Apple-Mail-62--486924356 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=WINDOWS-1252 I have a table that looks like = this:

CREATE TABLE = my_table (
=A0=A0=A0 pkey serial PRIMARY KEY,
=A0=A0=A0= fkey int NOT NULL REFERENCES my_other_table( pkey ),
=A0 =A0 = uid int=A0NOT NULL REFERENCES=A0user( pkey ),
=A0=A0=A0 seq1 = int,
=A0 =A0 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=A0 =A0 = =A0=A0 =A0| 1 =A0 =A0| 1 =A0 =A0| 1=A0 =A0 =A0=A0 =A0| = 1

2=A0 =A0 = =A0=A0 =A0| 1 =A0 =A0| 2 =A0 =A0| 2=A0 =A0 =A0=A0 =A0| 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=A0 =3D = 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 (
=A0 =A0 =A0 =A0 SELECT mt2.player_id, = mt2.order_no
=A0 =A0 =A0 =A0 FROM my_table AS mt2
=A0 = =A0 =A0 =A0 WHERE mt2.fkey =3D mt1.fkey
=A0 =A0 =A0 =A0 AND = mt2.seq2 =3D mt1.seq2
=A0 =A0 =A0 =A0 GROUP BY mt2.seq1, = mt2.uid
=A0 =A0 =A0 =A0 ORDER BY mt2.seq1 ASC
=A0 =A0 = =A0 =A0 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=99


http://www.sitening.com/

=

110 30th Avenue North, Suite = 6

Nashville, TN 37203-6320

615-260-0005

=

= --Apple-Mail-62--486924356--