Re: Can't get expected SELECT GROUP BY results

Peter Brawley Fri, 26 Dec 2008 11:33:58 -0800

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 andcritterXYZ for
each critterName and return one row for each unique critterName.

For examples & discussion see "Within-group aggregates" athttp://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 andcritterXYZ 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 / VirusDatabase: 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.comVersion: 8.0.176 / Virus Database: 270.9.19/1853 - Release Date: 12/17/2008 8:31 AM

Re: Can't get expected SELECT GROUP BY results

Reply via email to