SGreenunimin.com
Date: Fri Jul 01 2005 - 16:01:09 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

Seth Leonard <sethsethleonard.com> wrote on 07/01/2005 04:43:05 PM:

>
> I constantly bump up against this issue and am looking for a good
> solution. I want a query that will return multiple rows, but only one
> row per unique identifier in one field.
>
>
>
> For instance, if I have the following table:
>
>
>
> Name | Food | Eat_Date
>
> Bob | Hot Dog | 2005-06-25
>
> Jan | Pizza | 2005-06-27
>
> Jim | Salad | 2005-05-25
>
> Bob | Soup | 2005-06-03
>
> Ann | Toast | 2005-06-13
>
> Jim | Pizza | 2005-06-28
>
>
>
> I want to be able to pull the most recent food by each name without
> running multiple queries. I need a query that would give the results:
>
>
>
> Bob | Hot Dog | 2005-06-25
>
> Jan | Pizza | 2005-06-27
>
> Ann | Toast | 2005-06-13
>
> Jim | Pizza | 2005-06-28
>
>
>
> Anyone have ideas of how to do this in one query?
>
>
>
> Thanks,
>
> Seth
>

Sorry, even the single-statement version of the solution to this kind of
query uses a subquery. So, it is technically two queries in one statement.
There is no single-query solution to this problem, yet (you might count
the max-concat hack....but I won't becaue it is so inefficient)

http://dev.mysql.com/doc/mysql/en/example-maximum-column-group-row.html

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]