CODEWITHSUNDEEP
mysqlsqlgroup-bysql-order-by
Dylan Cross
Dylan Cross

Reputation: 5986

Group by and get the latest row from each group

I want to return only the newest rows that have a different video_id. I have been having trouble getting this to work no matter which way I try it... I have done this before and it isn't that difficult but for some reason my particular query will not work.

My table/results:

enter image description here

I have been trying this: 

SELECT * FROM user_video_history 
WHERE `user_id` = $db_safe_user_id 
GROUP BY video_id 
ORDER BY `date` DESC LIMIT 3

I have also tried this: 

SELECT *
FROM (SELECT *
        FROM user_video_history
        WHERE `user_id` = $db_safe_user_id
        ORDER BY `date` DESC) 
GROUP BY `video_id`
ORDER BY `date` DESC

Upvotes: 2

Views: 75

Answers (2)

Jay Kumar
Jay Kumar

Reputation: 34

You cannot use select * when you GROUP BY

Try this... 

SELECT 
    A . *
FROM
    user_video_history A,
    (SELECT 
        video_id, max(date) maxdate
    FROM
        user_video_history
    WHERE
        `user_id` = $db_safe_user_id
    GROUP BY video_id) B
where
    A.video_id = B.video_id
        and A.date = B.maxdate
order by A.date DESC
limit 3

Upvotes: 1

Leandro Papasidero
Leandro Papasidero

Reputation: 3738

Try this:

SELECT 
    t1 . *
FROM
    user_video_history t1
        JOIN
    (SELECT 
        id, MAX(`date`) `date`
    FROM
        user_video_history
    GROUP BY user_id) t2 ON t1.user_id = t2.user_id
        AND t1.`date` = t2.`date`
    LIMIT 3

Upvotes: 0

Related Questions