StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>This is a <a href="/questions/tagged/gaps-and-islands" class="post-tag" title="show questions tagged 'gaps-and-islands'" rel="tag">gaps-and-islands</a> problem, and here's another method of solving it, which also uses variables:</p> <pre><code>SELECT MIN(a) AS series_start, MAX(a) AS series_end, MAX(a) - MIN(a) + 1 AS series_count FROM ( SELECT a, @r := @r + 1 AS r FROM yourtable, (SELECT @r := 0) AS x ORDER BY a ) s GROUP BY a - r ORDER BY a - r ; </code></pre> <p>This is how it works.</p> <p>The subquery assigns row numbers to the table rows and returns this row set:</p> <pre><code> a r -- -- 1 1 2 2 3 3 5 4 6 5 7 6 9 7 10 8 11 9 12 10 </code></pre> <p>In this case the <code>r</code> column, which stores the row numbers, happens to match the <code>id</code> column in your data sample, but I'm assuming that in general the <code>id</code> column may have gaps, and for that reason it cannot be used here.</p> <p>The main query groups the results by the difference between <code>r</code> and <code>a</code>: for sequential values, it will always be the same:</p> <pre><code> a r a - r -- -- ----- 1 1 0 2 2 0 3 3 0 5 4 1 6 5 1 7 6 1 9 7 2 10 8 2 11 9 2 12 10 2 </code></pre> <p>and that allows us to group such rows together. All that remains at this point is to get the minimim, maximum and count, which gives you this output:</p> <pre><code>series_start series_end series_count ------------ ---------- ------------ 1 3 3 5 7 3 9 12 4 </code></pre> <p>A SQL Fiddle demonstration of this query, for which I've borrowed @sgeddes's schema, can be found <a href="http://sqlfiddle.com/#!2/05484/3" rel="nofollow">here</a>.</p> <hr> <p><strong>UPDATE</strong></p> <p>As numeric variables cannot be used (according to comments), you could assign row numbers using a triangular self-join, but it will be much less efficient than using a variable. Anyway, here's the modified version, changes to the previous query being highlighted in bold:</p> <pre>SELECT MIN(a) AS series_start, MAX(a) AS series_end, MAX(a) - MIN(a) + 1 AS series_count FROM ( SELECT data.a, COUNT(*) AS r FROM yourtable <b>AS data INNER JOIN yourtable AS tally ON data.id >= tally.id GROUP BY data.a</b> ) s GROUP BY a - r ORDER BY a - r ;</pre> <p>The approach itself remains unchanged: the subquery returns a ranked row set, which is then processed same as previously.</p> <p>A SQL Fiddle demo for the modified query is available <a href="http://sqlfiddle.com/#!2/05484/5" rel="nofollow">here</a>.</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