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