>What I want to do is SELECT only the most recent dateTime and critterXYZ for
>each critterName and return one row for each unique critterName.
For examples & discussion see "Within-group aggregates" at
http://localhost/artful/infotree/queries.php.
PB
-----
Fish Kungfu wrote:
> I have a dataset like this:
>
>
> +------------+------------------------+----------------------------------+---------------------+
> | regionName | critterName | critterXYZ | dateTime
> |
>
> +------------+------------------------+----------------------------------+---------------------+
> | yard | mouse | <147.94594, 206.72768, 58.80204> |
> 2008-12-2600:23:16 |
> | yard | mouse | <173.55687, 209.21608, 58.80544> |
> 2008-12-2600:24:16 |
> | yard | mouse | <96.77573, 127.38607, 44.53227> |
> 2008-12-2600:40:19 |
> | yard | mouse | <105.78719, 93.31913, 42.96943> |
> 2008-12-2600:40:30 |
> | yard | mouse | <105.78719, 93.31913, 42.96976> |
> 2008-12-2600:40:57 |
> | yard | mouse | <108.89838, 64.90623, 42.96977> |
> 2008-12-2600:41:57 |
> | yard | mouse | <121.04697, 70.54140, 76.25817> |
> 2008-12-2600:42:16 |
> | yard | mouse | <121.04697, 70.54140, 62.62100> |
> 2008-12-2600:42:30 |
> | yard | cat | <71.00000, 27.00000, 43.01170> |
> 2008-12-2601:06:30 |
> | yard | dog | <219.57300, 84.17950, 60.26318> |
> 2008-12-2601:06:37 |
> | yard | cat | <73.91701, 29.62952, 43.01169> |
> 2008-12-2601:07:30 |
> | yard | dog | <73.60881, 31.26160, 43.08546> |
> 2008-12-2601:07:30 |
> | yard | dog | <108.08045, 23.84270, 43.15333> |
> 2008-12-2601:08:30 |
> | yard | cat | <109.53104, 22.43899, 43.12498> |
> 2008-12-2601:08:30 |
> | yard | cat | <109.53104, 22.43899, 43.12498> |
> 2008-12-2601:08:57 |
> | yard | dog | <108.08045, 23.84270, 43.15333> |
> 2008-12-2601:08:57 |
> | yard | cat | <109.53104, 22.43899, 43.12498> |
> 2008-12-2601:09:30 |
> | yard | dog | <108.08045, 23.84270, 43.15333> |
> 2008-12-2601:09:30 |
> | yard | cat | <109.53104, 22.43899, 43.12498> |
> 2008-12-2601:09:57 |
> | yard | dog | <108.08045, 23.84270, 43.15333> |
> 2008-12-2601:09:57 |
> | yard | dog | <108.08045, 23.84270, 43.15333> |
> 2008-12-2601:10:30 |
> | yard | cat | <109.53104, 22.43899, 43.12498> |
> 2008-12-2601:10:30 |
> | yard | cat | <109.53104, 22.43899, 43.12498> |
> 2008-12-2601:10:57 |
> | yard | dog | <108.08045, 23.84270, 43.15333> |
> 2008-12-2601:10:58 |
> | yard | dog | <108.08045, 23.84270, 43.15333> |
> 2008-12-2601:11:30 |
> | yard | cat | <109.53104, 22.43899, 43.12498> |
> 2008-12-2601:11:30 |
> | yard | cat | <109.55525, 22.47036, 43.12509> |
> 2008-12-2601:11:57 |
> | yard | dog | <108.08045, 23.84270, 43.15333> |
> 2008-12-2601:11:58 |
> | yard | cat | <109.90359, 22.44845, 43.47617> |
> 2008-12-2601:12:30 |
> | yard | dog | <110.05429, 22.43897, 43.38527> |
> 2008-12-2601:12:30 |
> | yard | dog | <110.05429, 22.43897, 43.31427> |
> 2008-12-2601:12:57 |
> | yard | cat | <109.83472, 22.45278, 43.29317> |
> 2008-12-2601:12:58 |
> | yard | dog | <110.05429, 22.43897, 43.31427> |
> 2008-12-2601:13:30 |
> | yard | cat | <109.83472, 22.45278, 43.29317> |
> 2008-12-2601:13:30 |
> | yard | dog | <110.05429, 22.43897, 43.31427> |
> 2008-12-2601:13:57 |
> | yard | cat | <109.83472, 22.45278, 43.29317> |
> 2008-12-2601:13:58 |
> | yard | dog | <110.05429, 22.43897, 43.31427> |
> 2008-12-2601:14:30 |
> | yard | cat | <109.83472, 22.45278, 43.29317> |
> 2008-12-2601:14:30 |
> | yard | dog | <110.05429, 22.43897, 43.31427> |
> 2008-12-2601:14:57 |
>
> *************************************************************************************
>
> What I want to do is SELECT only the most recent dateTime and critterXYZ for
> each critterName and return one row for each unique critterName.
>
> So far I have this SELECT statement:
>
> mysql> SELECT regionName,critterName,critterXYZ,MAX(dateTime) FROM
> critterTrackerMain WHERE dateTime LIKE CONCAT(DATE(NOW()),'%') GROUP BY
> critterName ORDER BY critterName ASC;
>
> Which returns the most recent dateTime, but it's returning the first
> critterXYZ, not the most recent:
>
>
> +------------+------------------------+----------------------------------+---------------------+
> | regionName | critterName | critterXYZ |
> MAX(dateTime) |
>
> +------------+------------------------+----------------------------------+---------------------+
> | yard | dog | <219.57300, 84.17950, 60.26318> |
> 2008-12-2601:14:57 |
> | yard | cat | <71.00000, 27.00000, 43.01170> |
> 2008-12-2613:08:01 |
> | yard | mouse | <147.94594, 206.72768, 58.80204> |
> 2008-12-2600:42:30 |
>
> +------------+------------------------+----------------------------------+---------------------+
>
>
> This is what I want the SELECT to return but it's not working.
>
>
> +------------+------------------------+----------------------------------+---------------------+
> | regionName | critterName | critterXYZ |
> MAX(dateTime) |
>
> +------------+------------------------+----------------------------------+---------------------+
> | yard | dog | <110.05429, 22.43897, 43.31427> |
> 2008-12-2601:14:57 |
> | yard | cat | <109.83472, 22.45278, 43.29317> |
> 2008-12-2601:14:30 |
> | yard | mouse | <121.04697, 70.54140, 62.62100> |
> 2008-12-2600:46:16 |
>
> +------------+------------------------+----------------------------------+---------------------+
>
> Any advice would be greatly appreciated.
>
>
> Cheers....Fish
>
>
> ------------------------------------------------------------------------
>
>
> Internal Virus Database is out of date.
> Checked by AVG - http://www.avg.com
> Version: 8.0.176 / Virus Database: 270.9.19/1853 - Release Date: 12/17/2008 8:31 AM
>
>
