Getting a Value and an Average Value of Previous 20 Records in One Query

[Albert Padley]

I have been struggling with this issue most of the day. I can get the result
I need by using 2 queries, but that takes way too long. I'm trying to see if
there is a way to get the same result within a single query.

Here's the table
CREATE TABLE `log` (
  `id` int(14) NOT NULL auto_increment,
  `VarName` varchar(255) NOT NULL,
  `TimeString` varchar(255) NOT NULL,
  `VarValue` decimal(25,6) NOT NULL
 )

 The log table has 1 row added each minute of the day. For each VarValue I
also need the average value of the 20 previous rows.

 My 2 step solution looks like this:

 $phs = $db->get_results("SELECT VarValue, TimeString FROM log WHERE VarName
= 'xyz' AND SUBSTR(TimeString,1,10) = CURDATE() ORDER BY TimeString ASC");

foreach($phs as $ph) {
$myvalue = $db->get_var("SELECT AVG(VarValue) FROM log WHERE VarName = 'xyz'
AND TimeString <= '".$ph->TimeString."' ORDER BY TimeString DESC LIMIT 20");
}

I have tried to figure  a way using join as well as subselects, but haven't
hit on the right solution yet.

I appreciate some direction.

Thanks.

Al