mySQL ordering and distinct

Question

I have a review system on my website where users (profiles) can leave reviews for others. This part works fine.

But on my home page i want to get 10 of the latest reviews BUT i don't want to duplicate any users/profiles (i.e if a user has 2 reviews i only want the most recent)

Adding DISTINCT does not do this as all reviews/rows are unique. (i do add DISTINCT though as some users submit duplicate reviews...)

I have tried GROUP BY ProfileID but this does not necessarily return the most recent review and i can not work out how to do this. (GROUP BY kind of has the desired effect, but i think may be mixing up the data and is not getting the results in date order)

The below (simplified) SQL is what i currently have, but this brings back more than 1 review per user (if they have more than 1)

SELECT DISTINCT LEFT(r.ReviewText, 100) as Review, r.ReviewRating, r.ReviewDate,
p.ProfileName, p.Location, p.ProfileID,
((date_format(now(),'%Y') - date_format(p.DateOfBirth,'%Y')) - (date_format(now(),'00-%m-%d') < date_format(p.DateOfBirth,'00-%m-%d'))) AS Age
FROM tReview r
INNER JOIN tProfiles p ON p.ProfileID = r.tProfiles_ProfileID                                                       
WHERE r.ReviewWithdrawn = 0
  AND r.ReviewEnabled = 1
  AND p.Enabled = 1
ORDER BY r.ReviewDate DESC                    
LIMIT 10

Any ideas (without filtering in php) as to how i can retrieve 10 results, in date order but only including each user once (so if the user has 2 reviews only the most recent will be returned)?

did you already see this SO post ? http://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group — Randyka Yudhistira, Jan 26 '15 at 04:29
@RandykaYudhistira thanks, sounds just like what i need, although they explained it better then me. Thanks Again! — Ford, Jan 26 '15 at 04:33
@RandykaYudhistira thanks but i cant get any method to work for me :-( — Ford, Jan 26 '15 at 05:17

score 1 · Accepted Answer · answered Jan 26 '15 at 04:34

You can first select max date per user from reviews and then join back to itself and profiles to grab all other columns.

SELECT LEFT(r.ReviewText, 100) as Review, 
       r.ReviewRating, r.ReviewDate, p.ProfileName, p.Location, p.ProfileID,
       ((date_format(now(),'%Y') - date_format(p.DateOfBirth,'%Y')) - 
        (date_format(now(),'00-%m-%d') < date_format(p.DateOfBirth,'00-%m-%d'))) AS Age
  FROM
(
  SELECT p.ProfileID, MAX(r.ReviewDate) ReviewDate
    FROM tReview r JOIN tProfiles p 
      ON r.tProfiles_ProfileID = p.ProfileID
   WHERE r.ReviewWithdrawn = 0
     AND r.ReviewEnabled = 1
     AND p.Enabled = 1
   GROUP BY p.ProfileID
   ORDER BY ReviewDate DESC
   LIMIT 10
) q JOIN tReview r 
      ON q.ProfileID = r.tProfiles_ProfileID
     AND q.ReviewDate = r.ReviewDate 
    JOIN tProfiles p 
      ON r.tProfiles_ProfileID = p.ProfileID

mySQL ordering and distinct

1 Answers1