StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Now that I think that I understand your problem, based on your comment response to OMG Ponies, I think that I have a set-based solution. The idea is to first find the start of any chains based on the title. The start of a chain is going to be defined as any row where there is no match within three seconds prior to that row:</p> <pre><code>SELECT MT1.my_id, MT1.title, MT1.my_time FROM My_Table MT1 LEFT OUTER JOIN My_Table MT2 ON MT2.title = MT1.title AND ( MT2.my_time < MT1.my_time OR (MT2.my_time = MT1.my_time AND MT2.my_id < MT1.my_id) ) AND MT2.my_time >= MT1.my_time - INTERVAL 3 SECONDS WHERE MT2.my_id IS NULL </code></pre> <p>Now we can assume that any non-chain starters belong to the chain starter that appeared before them. Since MySQL doesn't support CTEs, you might want to throw the above results into a temporary table, as that would save you the multiple joins to the same subquery below.</p> <pre><code>SELECT SQ1.my_id, COUNT(*) -- You didn't say what you were trying to calculate, just that you needed to group them FROM ( SELECT MT1.my_id, MT1.title, MT1.my_time FROM My_Table MT1 LEFT OUTER JOIN My_Table MT2 ON MT2.title = MT1.title AND ( MT2.my_time < MT1.my_time OR (MT2.my_time = MT1.my_time AND MT2.my_id < MT1.my_id) ) AND MT2.my_time >= MT1.my_time - INTERVAL 3 SECONDS WHERE MT2.my_id IS NULL ) SQ1 INNER JOIN My_Table MT3 ON MT3.title = SQ1.title AND MT3.my_time >= SQ1.my_time LEFT OUTER JOIN ( SELECT MT1.my_id, MT1.title, MT1.my_time FROM My_Table MT1 LEFT OUTER JOIN My_Table MT2 ON MT2.title = MT1.title AND ( MT2.my_time < MT1.my_time OR (MT2.my_time = MT1.my_time AND MT2.my_id < MT1.my_id) ) AND MT2.my_time >= MT1.my_time - INTERVAL 3 SECONDS WHERE MT2.my_id IS NULL ) SQ2 ON SQ2.title = SQ1.title AND SQ2.my_time > SQ1.my_time AND SQ2.my_time <= MT3.my_time WHERE SQ2.my_id IS NULL </code></pre> <p>This would look much simpler if you could use CTEs or if you used a temporary table. Using the temporary table might also help performance.</p> <p>Also, there will be issues with this if you can have timestamps that match exactly. If that's the case then you will need to tweak the query slightly to use a combination of the id and the timestamp to distinguish rows with matching timestamp values.</p> <p><strong>EDIT:</strong> Changed the queries to handle exact matches by timestamp.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload