Re: nested select statement syntax error

[Steven Becker]

What about a table that contains a timestamp and a total value (generally increasing)?

+-------+-----------+------+-----+-------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+-------------------+-------+
| date | timestamp | YES | | CURRENT_TIMESTAMP | |
| recv | int(11) | YES | | NULL | |
+-------+-----------+------+-----+-------------------+-------+

Goal: find the maximum difference between sequential "recv" totals for a period of time.

The database contains data for every 5 minute interval, so sequential differences would always be based on this interval.

For example, if the database contains:
timestamp --- recv
01/01/70 00:00 --- 000
01/01/70 00:05 --- 003
01/01/70 00:10 --- 007
01/01/70 00:15 --- 010
...
01/02/70 04:20 --- 100
01/02/70 04:25 --- 103
01/02/70 04:30 --- 104
01/02/70 04:35 --- 106

The maximum differences for the days are:
01/01/70 --- 4 (7-3=4)
01/02/70 --- 3 (103-100=3)

Which would be more efficient, using a nested query or using a join?

Can an SQL statement be made to do the same thing for maximum differences in an hour (as opposed to a day)?

Any help would be greatly appreciated!
Thanks!

PS: In case you're wondering, this db stores the total bytes recv'd by my non-snmp router. Gnuplot generates traffic graphs but is bogged down by massive data sets. The above problem would still find the peak points for yearly timeframes while generating much smaller data sets.



Edited 3 time(s). Last edit at 07/16/2006 12:49PM by Steven Becker.