StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>So we have a table of timecodes which, for the sake of argument, might look like this...</p> <pre><code>CREATE TABLE timecodes(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,timecode INT NOT NULL); INSERT INTO timecodes (timecode) VALUES (2),(3),(4),(4),(6),(13),(14),(17),(18),(18),(21),(25),(28),(28); SELECT * FROM timecodes; +----+----------+ | id | timecode | +----+----------+ | 1 | 2 | | 2 | 3 | | 3 | 4 | | 4 | 4 | | 5 | 6 | | 6 | 13 | | 7 | 14 | | 8 | 17 | | 9 | 18 | | 10 | 18 | | 11 | 21 | | 12 | 25 | | 13 | 28 | | 14 | 28 | +----+----------+ </code></pre> <p>Now, this question is in two parts. The first part concerns obtaining a random result from within the first <em>n</em> results. One way of doing that (although probably not the fastest way) is like this...</p> <pre><code> SELECT @seed := x.timecode FROM timecodes x JOIN timecodes y ON y.id <= x.id GROUP BY x.id HAVING COUNT(*) <= 5 ORDER BY RAND() LIMIT 1; +---------------------+ | @seed := x.timecode | +---------------------+ | 4 | +---------------------+ </code></pre> <p>This query generates a seed (in this case '4'), which can be ploughed back into subsequent queries, e.g.;</p> <pre><code>SELECT @seed := MIN(y.timecode) FROM timecodes x JOIN timecodes y ON y.timecode >= x.timecode + 3 WHERE x.timecode = @seed; 1st iteration +--------------------------+ | @seed := MIN(y.timecode) | +--------------------------+ | 13 | +--------------------------+ 2nd iteration +--------------------------+ | @seed := MIN(y.timecode) | +--------------------------+ | 17 | +--------------------------+ 3rd iteration +--------------------------+ | @seed := MIN(y.timecode) | +--------------------------+ | 21 | +--------------------------+ 4th iteration +--------------------------+ | @seed := MIN(y.timecode) | +--------------------------+ | 25 | +--------------------------+ 5th iteration +--------------------------+ | @seed := MIN(y.timecode) | +--------------------------+ | 28 | +--------------------------+ 6th iteration +--------------------------+ | @seed := MIN(y.timecode) | +--------------------------+ | NULL | +--------------------------+ </code></pre> <p>This can be wrapped up in a sproc, or some application level code that says 'do the first thing then, while @seed is NOT NULL, do the second thing' - but that's a step beyond my pay grade. </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