From: Peter Brawley Date: December 26 2008 7:30pm Subject: Re: Can't get expected SELECT GROUP BY results List-Archive: http://lists.mysql.com/mysql/215733 Message-Id: <495530BB.3070902@earthlink.net> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="------------080509020306050809010300" --------------080509020306050809010300 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit >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 > > --------------080509020306050809010300--