Save every Nth record of a MySQL query while keeping descending limit

Question

Is there a way to return every Nth record of a MySQL request while keeping my descending limit? Otherwise, is it possible to only save every Nth request into my PHP array? The data is eventually echoed to javascript to supply data to a Google Chart, and obviously 10080 records does not create a smooth graph.

$myquery = "SELECT * 
FROM ( 
    SELECT 
        @row := @row +1 AS rownum, dateTime, outTemp, dewpoint
    FROM ( 
        SELECT @row :=0) r, archive 
    ) ranked 
    WHERE rownum % 5 = 1";

$query = mysql_query($myquery);

 if ( ! $query ) 
 {
        echo mysql_error();
        die;
}

$table=array();
$table['cols'] = array(
    // Chart Labels (i.e. column headers)
    array('label' => 'dateTime', 'type' => 'number'),
    array('label' => 'Temp', 'type' => 'number'),
    array('label' => 'Dew', 'type' => 'number')
);

$rows = array();

while($r = mysql_fetch_assoc($query))
{
    $temp = array();
    $temp[] = array('v' => (int) $r['dateTime']); 
    $temp[] = array('v' => (int) $r['outTemp']);
    $temp[] = array('v' => (int) $r['dewpoint']);
    $rows[] = array('c' => $temp);
}

$table['rows'] = $rows;
$jsonTable = json_encode($table);

UPDATE:

I can select every Nth row using the instructions at How do you select every n-th row from mysql but it does not solve how to keep my descending filter. I am trying to select the last x rows of my dataset in a manner like using DESC LIMIT 10

Stop using `mysql_` functions as they are deprecated: http://php.net/manual/en/migration55.deprecated.php — D4V1D, Mar 25 '15 at 16:57
Seriously, why haven't you googled this? First result on google - http://stackoverflow.com/questions/858746/how-do-you-select-every-n-th-row-from-mysql — Styphon, Mar 25 '15 at 16:59
That returns an error from the SQL server when used with the DESC LIMIT — WxPilot, Mar 25 '15 at 17:09

score 0 · Answer 1 · answered Mar 25 '15 at 17:30

0

SELECT @row_number := NULL;
SELECT
    @row_number := IFNULL(@row_number, 0) + 1 AS row_number,
    Column1,
    Column2,
    ColumnN
FROM the_table
WHERE @row_number % 2 = 0
ORDER BY Column1

answered Mar 25 '15 at 17:30

J Hock

129
3

score 0 · Accepted Answer · answered Mar 25 '15 at 19:48

I solved the issue by getting a row count and setting the limits based on the row count, N ($interval in this case), and the span of data required. This solution puts the data in descending order, within the limits, while keeping the data itself in the correct order for a Google Charts plot.

// Get a row count ... ... ...
$count = mysql_query("select count(1) FROM archive");
$grabCount = mysql_fetch_array($count);

 if ( ! $count ) 
 {
        echo mysql_error();
        die;
}




// ROW BOUNDARIES  ... ... ...
$hour = 60;
$day = 1440;
$week = 10080;
$month = 43200;
$year = 525600;

$timeSpan = $day;
$interval = 30;

$filter = $timeSpan / $interval;
$rows = (int) ($grabCount[0] / $interval);

$rowStart = ($rows - $filter);
$rowEnd = $rows;


// Query the Database for what you want
$myquery = "SELECT * 
FROM ( 
    SELECT 
        @row := @row +1 AS rownum, dateTime, outTemp, dewpoint
    FROM ( 
        SELECT @row :=0) r, archive 
    ) ranked 
WHERE rownum % $interval = 1 LIMIT $rowStart,$rowEnd";

Save every Nth record of a MySQL query while keeping descending limit

2 Answers2