List:General Discussion« Previous MessageNext Message »
From:Fish Kungfu Date:December 26 2008 7:05pm
Subject:Can't get expected SELECT GROUP BY results
View as plain text  
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

Thread
Can't get expected SELECT GROUP BY resultsFish Kungfu26 Dec
  • Re: Can't get expected SELECT GROUP BY resultsPeter Brawley26 Dec
    • Re: Can't get expected SELECT GROUP BY resultsPeter Brawley26 Dec
      • Re: Can't get expected SELECT GROUP BY resultsFish Kungfu26 Dec