Databases
StackOverflow2013
Postsdbo
POMySQL return every nth record from selected range
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQL return every nth record from selected range
    primarykey
    data
    text
    <blockquote> <p><strong>Possible Duplicate:</strong><br> <a href="https://stackoverflow.com/questions/858746/how-do-you-select-every-n-th-row-from-mysql">How do you select every n-th row from mysql</a> </p> </blockquote> <p>Here is the problem and my approach so far. I have a php <strong>mysql_query</strong> from a table that can contain anything between 2000 to a few million rows. The JavaScript visualization library that I am using can not process array larger than 4-5 thousand elements. So I just sample every nth element. In php I query the MySQL database for the number of rows. If that number is greater than 5000 I send one type of <strong>$query_every_n</strong> that will return every nth element from the first two columns of the table. If it is less I just get the whole table for the entire range.</p> <pre><code> &lt;?php .... $query_ct="select count(*) from ". $table_selected; //Hey MySQL how many u got in that table_selected $result_ct=mysql_query($query_ct); while($row=mysql_fetch_array($result_ct)) { $nct=$row[0];//SQL said there are $nct rows from the query_ct } if ($nct&gt;5000 ){ $rownum=intval($nct/5000); $query_evry_n="SELECT * FROM (SELECT @row:=@row+1 as rownum,". $table_selected.".* FROM (SELECT @row:=0 )r,". $table_selected ." ) ranked WHERE rownum %".$rownum."=1"; } else{ $query_evry_n="SELECT * FROM ".$table_selected; } $result=mysql_query($query_evry_n); if($result) { $myarr=array(); while($row=mysql_fetch_array($result)){ $myarr[]=array($row['time'],$row[$field_selected]/100); }//end while ?&gt; </code></pre> <p>I would like to be able to do that entirely in MySQL. For example I have a table with 200 000 rows. PHP will query for data from a given range array($minVal,$maxVal) for example 100 000 to 150 000. And MySQL will return sample of every 50000/5000=10 points from that 50 000 range or whatever the case is. I made few attempts to solve this on my own but so far unsuccessfully. Thank you for your help.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USmirk
    plurals
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTDuplicate
    3. PL
      singulars
      1. LTLinked
    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. VO
      singulars
      1. POMySQL return every nth record from selected range
      1. This table or related slice is empty.
      1. VTModeratorReview
    1. COpossible duplicate of [How do you select every n-th row from mysql](http://stackoverflow.com/questions/858746/how-do-you-select-every-n-th-row-from-mysql) and [How do I retrieve every Nth record from a table?](http://stackoverflow.com/questions/1692509/how-do-i-retrieve-every-nth-record-from-a-table)
      singulars
      1. POMySQL return every nth record from selected range
      1. USMatt Ball
    2. COMy solution so far is taken from similar post from this forum. However I need to get every nth from a specified range and I would like to do the math for how big the n is inside MySQL. Combining these two things is not that trivial or straight forward for me.
      singulars
      1. POMySQL return every nth record from selected range
      1. USmirk
    3. COYou basically just need WHERE and LIMIT clauses.
      singulars
      1. POMySQL return every nth record from selected range
      1. USMatt Ball
 

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