Thank you, Peter! I'll give that a try. ~~Fish~~
On Fri, Dec 26, 2008 at 2:33 PM, Peter Brawley <peter.braw...@earthlink.net>wrote: > Sorry LOL, make that http://www.artfulsoftware.com/infotree/queries.php. > > PB > > ----- > > > Peter Brawley wrote: > > 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 > > > > > ------------------------------ > > > 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 > > > >
