Databases
StackOverflow2013
Postsdbo
PORandom results spaced in specific time (according to column data)
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PORandom results spaced in specific time (according to column data)
    primarykey
    data
    text
    <p>I've those 2 tables in MySQL<br> - "tag_name" which contains an unique <code>tag_name_id</code> of each individual created tag, and the respective <code>tag</code><br> - "tags" which contains also an unique <code>tags_id</code>, a <code>timecode</code> (time instant) and <code>group_id</code> (same tags can have different group_id)</p> <p>Now, what I'm trying to do is getting a random <code>timecode</code> somewhere in the first 10 seconds (<code>timecode &lt;= 10</code>). And after that, and according to that timecode extracted, try to select all the results that are distanced by 3 or more seconds. (all the results less than 3 seconds must be discarded).</p> <p>Example:<br> If I have these results in my database: </p> <pre><code>2,3,4,4,6,13,14,17,18,18,21,25,28,28,etc (timecodes) </code></pre> <p>I want to grab one of the first 10 randomly (lets say I pick the 4) and then I want to start sorting them randomly according to that time instant. ("randomly" because I want to change the order when same timecode instants appears ie: "4,4" because they are associated to different tags, so I want them to "switch" between them, so I can pick up a different one every time)<br> So the result query came in something like this: 4,13,17,21,25,28,etc</p> <p>I already have this query that returns me the random number, and this morning I have been trying to make a Select inside the Select because I think the answer is there, but I can't retrieve the results I want, and I also can't find a way to retrieve result distanced by 3...</p> <pre><code>SELECT tag_name.tag, ROUND(avg(timecode)) as timecode, group_id FROM tags INNER JOIN tag_name ON tag_name.tag_name_id = tags.tag_name_id WHERE tags.filename = 'filename.mp4' AND timecode &lt;= 10 GROUP BY group_id, tag_name.tag ORDER BY RAND() LIMIT 1 </code></pre> <p><a href="http://sqlfiddle.com/#!2/a4438/5" rel="nofollow">Here</a> is the SQLFiddle</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USzppinto
    1. USzppinto
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COThere's exactly one 'timecode' per second?
      singulars
      1. PORandom results spaced in specific time (according to column data)
      1. USStrawberry
    2. COYes! All 'timecodes' are inserted in seconds...
      singulars
      1. PORandom results spaced in specific time (according to column data)
      1. USzppinto
    3. COThat's not what i said! Now I'm very confused. Do you want your first result to be a random result from amongst the first ten entries (i.e. 2 through 18) OR from within the first 10 seconds (i.e. 2 through 6?)
      singulars
      1. PORandom results spaced in specific time (according to column data)
      1. USStrawberry
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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