Re: nested select statement syntax error

[Steven Becker]

Thanks for the headstart!

The syntax below returns the date and max differential for the current day. Both instances of NOW() are required to decrease query time.

SELECT DATE(t2.date),(t2.recv-t1.recv) AS diff
FROM cells AS t1
INNER JOIN cells AS t2 ON TIME_TO_SEC(TIMEDIFF(t2.date,t1.date))<=7*60 AND t2.date>t1.date
WHERE DATE(t1.date)=DATE(NOW()) AND DATE(t2.date)=DATE(NOW())
ORDER BY diff DESC
LIMIT 1;

+---------------+--------+
| DATE(t2.date) | diff |
+---------------+--------+
| 2006-07-17 | 193854 |
+---------------+--------+
1 row in set (1.50 sec)

I can get the max for any other day by manually changing both instances of NOW() in the WHERE clause to include a subtracting interval. For example, the prior day would have "NOW()-INTERVAL 1 DAY" for both instances in the WHERE clause.

+---------------+--------+
| DATE(t2.date) | diff |
+---------------+--------+
| 2006-07-16 | 233948 |
+---------------+--------+
1 row in set (6.58 sec)

Without LIMIT 1, the resulting tables have many rows. What I need, however, is a table with a range of dates, but only one row per date.

+---------------+--------+
| DATE(t2.date) | diff |
+---------------+--------+
| 2006-07-16 | 233948 |
+---------------+--------+
| 2006-07-17 | 193854 |
+---------------+--------+

Am I going about this the wrong way or am I close? Is there a way to use nested queries or another inner join to return the single max for a range of days?

Thanks again!