From: Thomas F. O'Connell <tfo@sitening.com>
To: PgSQL - SQL <pgsql-sql@postgresql.org>
Subject: Grouping Too Closely
Date: Thu, 23 Jun 2005 14:16:36 -0500
Message-ID: <BBA34C36-E1D7-4166-A47F-BA0D563C48B9@sitening.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tfo@sitening.com
Subject: Re: Grouping Too Closely
In-Reply-To: <BBA34C36-E1D7-4166-A47F-BA0D563C48B9@sitening.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox