Re: You can't do this with MySQL!

Thomas 'PointedEars' Lahn wrote:

> PREPARE `stmt`
> FROM 'SET _at_avg := (
> SELECT AVG(`year`)
> FROM (SELECT `year` FROM `tmp` ORDER BY `year` LIMIT ?, 2) AS `tmp2`
> )';
>
> EXECUTE `stmt` USING _at_middle_index;
>
> PREPARE `stmt`
> FROM 'SET _at_middle := (
> SELECT `year` FROM `tmp` ORDER BY `year` LIMIT ?, 1
> )';
>
> EXECUTE `stmt` USING _at_middle_index;

Optimization if creating temporary tables is allowed:

   DROP TEMPORARY TABLE IF EXISTS `median`;

   PREPARE `stmt`
   FROM 'CREATE TEMPORARY TABLE `median`

         SELECT `year` FROM `tmp` ORDER BY `year` LIMIT ?, 2';   

   EXECUTE `stmt` USING _at_middle_index;

   DEALLOCATE PREPARE `stmt`;

   SET _at_avg := (SELECT AVG(`year`) FROM `median`);    SET _at_middle := (SELECT `year` FROM `median` LIMIT 0, 1);

   DROP TEMPORARY TABLE `median`;

   SET _at_median := (SELECT IF(@count % 2 = 0, @avg, @middle));

-- 
PointedEars
Zend Certified PHP Engineer 
<http://www.zend.com/en/yellow-pages/ZEND024953> | Twitter: _at_PointedEars2
Please do not cc me. / Bitte keine Kopien per E-Mail.