You want the rows containing the maximum post_d for each movies_id group.
As you've seen, this is tricky. Fortunately, the manual suggests three
solutions to this problem
<http://dev.mysql.com/doc/mysql/en/example-Maximum-column-group-row.html>.
Michael
Seth Leonard wrote:
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]
