rendered paste bodyOK, here is my table `pong`
mysql> select * from pong order by id desc limit 25;
+------+--------------+---------------------+---------------------+------------+--------+----+
| id | node | datetime | version | gateway | metric | fb |
+------+--------------+---------------------+---------------------+------------+--------+----+
| 6583 | 5.35.10.16 | 2005-11-15 09:19:00 | cobianet-2005-11-01 | 5.16.16.25 | 2639 | 0 |
| 6582 | 5.33.249.220 | 2005-11-15 09:18:18 | cobianet-2005-11-01 | 5.16.16.25 | 4347 | 0 |
| 6581 | 5.35.10.21 | 2005-11-15 09:00:44 | cobianet-2005-11-01 | 5.16.16.25 | 8027 | 0 |
| 6580 | 5.53.196.142 | 2005-11-15 08:58:33 | cobianet-2005-11-01 | 5.16.16.25 | 4683 | 0 |
| 6579 | 5.35.10.11 | 2005-11-15 08:54:25 | cobianet-2005-11-01 | 5.16.16.25 | 6021 | 0 |
| 6578 | 5.16.16.25 | 2005-11-15 08:40:44 | cobianet-2005-11-01 | gateway | | 0 |
| 6577 | 5.35.10.25 | 2005-11-15 08:35:05 | cobianet-2005-11-01 | 5.16.16.25 | 3324 | 0 |
| 6576 | 5.35.10.22 | 2005-11-15 08:34:10 | cobianet-2005-11-01 | 5.16.16.25 | 2913 | 0 |
| 6575 | 5.33.249.215 | 2005-11-15 08:31:30 | cobianet-2005-11-01 | 5.16.16.25 | 2872 | 0 |
| 6574 | 5.53.192.238 | 2005-11-15 08:28:42 | cobianet-2005-11-01 | 5.16.16.25 | 3608 | 0 |
| 6573 | 5.35.10.16 | 2005-11-15 08:19:00 | cobianet-2005-11-01 | 5.16.16.25 | 4117 | 0 |
| 6572 | 5.33.249.220 | 2005-11-15 08:18:19 | cobianet-2005-11-01 | 5.16.16.25 | 6615 | 0 |
| 6571 | 5.35.10.23 | 2005-11-15 08:04:50 | cobianet-2005-11-01 | 5.16.16.25 | 19177 | 0 |
| 6570 | 5.35.10.21 | 2005-11-15 08:00:43 | cobianet-2005-11-01 | 5.16.16.25 | 6082 | 0 |
| 6569 | 5.53.196.142 | 2005-11-15 07:58:32 | cobianet-2005-11-01 | 5.16.16.25 | 2913 | 0 |
| 6568 | 5.35.10.11 | 2005-11-15 07:54:26 | cobianet-2005-11-01 | 5.16.16.25 | 5906 | 0 |
| 6567 | 5.16.16.25 | 2005-11-15 07:40:44 | cobianet-2005-11-01 | gateway | | 0 |
| 6566 | 5.35.10.25 | 2005-11-15 07:35:06 | cobianet-2005-11-01 | 5.16.16.25 | 3482 | 0 |
| 6565 | 5.35.10.22 | 2005-11-15 07:34:12 | cobianet-2005-11-01 | 5.16.16.25 | 5504 | 0 |
| 6564 | 5.33.249.215 | 2005-11-15 07:31:30 | cobianet-2005-11-01 | 5.16.16.25 | 2484 | 0 |
| 6563 | 5.53.192.238 | 2005-11-15 07:28:41 | cobianet-2005-11-01 | 5.16.16.25 | 1907 | 0 |
| 6562 | 5.35.10.16 | 2005-11-15 07:19:00 | cobianet-2005-11-01 | 5.16.16.25 | 2484 | 0 |
| 6561 | 5.33.249.220 | 2005-11-15 07:18:15 | cobianet-2005-11-01 | 5.16.16.25 | 11007 | 0 |
| 6560 | 5.35.10.23 | 2005-11-15 07:05:18 | cobianet-2005-11-01 | 5.16.16.25 | 11589 | 0 |
| 6559 | 5.35.10.21 | 2005-11-15 07:00:53 | cobianet-2005-11-01 | 5.16.16.25 | 7819 | 0 |
+------+--------------+---------------------+---------------------+------------+--------+----+
25 rows in set (0.00 sec)
Every hour each `node` updates the table with the different values, gateway, metric...
I want to query the table for each node's time since last update so I do this:
mysql> select MAX(id) as maxid,node,SEC_TO_TIME(UNIX_TIMESTAMP()-UNIX_TIMESTAMP(MAX(datetime))) from pong group by node;
+-------+--------------+-------------------------------------------------------------+
| maxid | node | SEC_TO_TIME(UNIX_TIMESTAMP()-UNIX_TIMESTAMP(MAX(datetime))) |
+-------+--------------+-------------------------------------------------------------+
| 6578 | 5.16.16.25 | 00:44:40 |
| 3216 | 5.33.249.214 | 330:46:07 |
| 6575 | 5.33.249.215 | 00:53:54 |
| 6582 | 5.33.249.220 | 00:07:06 |
| 6579 | 5.35.10.11 | 00:30:59 |
| 6583 | 5.35.10.16 | 00:06:24 |
| 6357 | 5.35.10.18 | 20:05:18 |
| 2710 | 5.35.10.20 | 375:15:36 |
| 6581 | 5.35.10.21 | 00:24:40 |
| 6576 | 5.35.10.22 | 00:51:14 |
| 6571 | 5.35.10.23 | 01:20:34 |
| 6577 | 5.35.10.25 | 00:50:19 |
| 3270 | 5.5.5.5 | 325:43:14 |
| 6574 | 5.53.192.238 | 00:56:42 |
| 6580 | 5.53.196.142 | 00:26:51 |
| 3275 | yea | 325:23:52 |
+-------+--------------+-------------------------------------------------------------+
16 rows in set (0.62 sec)
That works fine, now I also want to get each nodes most recent gateway, metric, version, and fb along with the amount of time since last update. So basically I want the query above including the results of:
select metric, gateway, version, fb from pong where id = $QUERYABOVE.maxid
all in one query, any ideas?