How can I get every n rows in MySQL?

Tags: php mysql mysqli
By : DavidM
Source: Stackoverflow.com
Question!

I have a table which contains data recorded every minute, so I have a row for each minute. When returning the data for processing, this accuracy is required for the last 6 hours but after that, a lower level of accuracy is sufficient, e.g. every 5 minutes.

I can return all the data into an array and then remove all but every 5th element but that requires all data to returned by MySQL and then read into the array first - quite a lot of data.

How can I return every nth row in MySQL? I have read this blog post which suggests using primaryKey % 5 = 0 where primaryKey is auto_increment but this

a) doesn't use indexes b) will only return primaryKey values which are divisible by 5 and in the case of deletions, may not actually be every 5th row

Can this be done just within the SQL query or will it require looping row by row through the result set using cursors?

I am using MySQLi in PHP to connect to the DB.

By : DavidM


Answers

The list of timestamps every 5 minutes:

SELECT
  MIN(logtimestamp) AS first_of_five_minutes
FROM tLog
GROUP BY
  DATE(logtimestamp),
  HOUR(logtimestamp),
  MINUTE(logtimestamp) - (MINUTE(logtimestamp) % 5)

Now, you can use this as a sub-select to get the requested log entries by joining logtimestamps to first_of_five_minutes on the . Of course, additional WHERE-clauses have to be replicated inside and outside so you get the "right" timestamps.

Also, note that this returns the first timestamp in every five-minute interval, wheras solutions directly using minutes%5 = 0 only return logentries which are actually on multiples of :05, which may fail if you have delays in recording logs or similar.



I'd suggest getting someone to put a twitter widget on the page, then you can sms/email or use a web browser to send your updates and they will automatically appear on the site.

Is it at all possible for you get someone to do that for you? Twittercard can be used to generate the code to drop in.



On a general basis, yes it's possible, that's what most blog engines and CMS are for. It's also fairly easy to develop an ad-hoc program if all you need is to be able to put an offline page. If what you mean however is you need to do today witout any access to the server, contacting the person hosting your site or your DNS are indeed your best chances.



This video can help you solving your question :)
By: admin