Mysql 3 record in 1 hr = 1 record in another table

[rolperez]

Hi!

I have a problem, i have a table, where the information of some devices are deposited if they have an alert, the structure of the table is this:

ID | SERVERID | TEMP | SENSOR | PROCESSED | DATETIME
1 | 12458788 | 23.3 | 1 | 0 |2012-01-16 16:50:01

I need to send the information to another table, if i have 3 records in 1 hour, the script sends only 1 record to another table or 2 followed records = 1 record in another table , do you have some ideas? i really really need help!

Thank for your time!!

[EDIT]

The table populates with various SERVERID's in an hour.....i need to send 1 record to another table for every 3 records in an hour of every serverid..... Thanks!!

[BluePanther]

What would this 1 record be?

Also, why would you want to do this? Why not just pull the data from the other table (I'm assuming both tables are in the same database) as you need it?

[rolperez]

Hi! Ihave 2 tables 'prealarms' and 'alarms'


the structure of the table 'prealarms' are this:
ID | SERVERID | TEMP | SENSOR | PROCESSED | DATETIME
1 | 12458788 | 23.3 | 1 | 0 |2012-01-16 16:50:01
2 | 12458789 | 24.3 | 1 | 0 |2012-01-16 16:55:01
3 | 12458786 | 21.3 | 1 | 0 |2012-01-16 16:55:01
4 | 12458788 | 21.3 | 1 | 0 |2012-01-16 16:55:01
etc..

and i need a query to separate the records by id and if there are three records in a lapse of one hour, send the 'serverid' to the 'alarms' table....only 1 record every 3 records found in a lapse of one hour...

Thanks!

[themousemaster]

Is what you are trying to accomplish

--- for every hour, if it has 3 or more records, I need to make a record in the other table

or is it

--- for every hour, I need to write (#records / 3 rounded down) records to the other table

or is it

--- if there exists any record for which 2 other records are within 60 minutes of it, write a record to the other table

?



(The best solution will differ depending on which it is)

[rolperez]

One server send 1 record every 5 minutes, then the maximum number of records for 1 servers in 'prealarms' are 12....every 3 records i need to send 1 record to 'alarms'....

the best are this i think:


"--- for evert hour, if it has more than 3 records, I need to make a record in the other table"

Thanks!

[rolperez]

any suggestions?

[rolperez]

I thik i find an answer, if you have something better, please reply.

The code i was find is this:

SELECT
prealarmas.preid,
prealarmas.serverid,
prealarmas.temp,
prealarmas.visto,
prealarmas.datetime,
HOUR(prealarmas.datetime) AS h, COUNT(*)
FROM
prealarmas
WHERE
prealarmas.serverid =  '12345678887' AND
prealarmas.visto =  '0'
GROUP BY
h

And if h is = or > 3 , insert bla, bla... into 'alarms'

What doy you think?

[themousemaster]

that would be similar to what I would have suggested.

You are grouping on "h" though, so you dont want to look for h >= 3, you want to look for COUNT(*) >= 3