unique by field issue

[Seth Leonard]

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

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

problem with distinct not solved by group by

[Seth Leonard]

I have three tables:

reviews
users
movies

I am trying to select the latest 4 reviews for DIFFERENT movies.

I can use the following query:

SELECT reviews.movies_id, movies.movie_title, users.name,
reviews.rating, reviews.post_d FROM reviews, users, movies WHERE
reviews.user_id = users.user_id and reviews.movies_id = movies.movie_id
ORDER BY post_d DESC LIMIT 4

However, this can return the same movie twice if any of the last 4
reviews are of the same movie.

DISTINCT is no help because I only want a distinct on movies_id, not
the whole row.

GROUP BY movies_id is no help because it takes the oldest review from
the unique movies_id.

Does anyone have an idea where I can take the most recent 4 rows that
have a different movies_id without doing extra processing work in PHP?

Thanks,
Seth

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]