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
