CODEWITHSUNDEEP
mysqldatabase
Bustikiller
Bustikiller

Reputation: 2498

Retrieve one out of every n records

I have a table containing thousands of records representing the temperature of a room in a certain moment. Up to now I have been rendering a client side graph of the temperature with JQuery. However, as the amount of records increases, I think it makes no sense to provide so much data to the view, if it is not going to be able to represent them all in a single graph.

I would like to know if there exists a single MySQL query that returns one out of every n records in the table. If so, I think I could get a representative sample of the temperatures measured during a certain lapse of time.

Any ideas? Thanks in advance.

Edit: add table structure.

CREATE TABLE IF NOT EXISTS `temperature` (
  `nid` int(10) unsigned NOT NULL COMMENT 'Node identifier',
  `temperature` float unsigned NOT NULL COMMENT 'Temperature in Celsius degrees',
  `timestamp` int(10) unsigned NOT NULL COMMENT 'Unix timestamp of the temperature record',
  PRIMARY KEY (`nid`,`timestamp`)

)

Upvotes: 0

Views: 40

Answers (2)

Paul Stanley
Paul Stanley

Reputation: 4098

You could do this, where the subquery is your query, and you add a row number to it:

SET @rows=0;
SELECT * from(
   SELECT @rows:=@rows+1 AS rowNumber,nid,temperature,`timestamp`
   FROM temperature
) yourQuery
WHERE MOD(rowNumber, 5)=0

The mod would choose every 5th row: The 5 here is your n. so 5th row, then 10th, 15th etc.

Upvotes: 2

davejal
davejal

Reputation: 6133

Not really sure what your asking but you have multiple options

  1. You can limit your results to n (n representing the amount of temperatures you want to display)

just a simple query with the limit in the end:

select * from tablename limit 1000
  1. You could use a time/date restraint so you display only the results of the last n days.

Here is an example that uses date functions. The following query selects all rows with a date_col value from within the last 30 days:

mysql> SELECT something FROM tbl_name
    -> WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= date_col;
  1. You could select an average temperature of a certain period, the shorter the period the more results you'll get. You can group by date, yearweek, month etc. to "create the periods"

Upvotes: 0

Related Questions