user2597933 user2597933 - 1 year ago 57
MySQL Question

MySQL Grouping sort issue

I have a table of users watch history. I log the 'content_id', the 'user_id' and add a timestamp in a table called 'watch_history' every time content is viewed. Even if the same content is watched multiple times in a row, every hit is logged.

I need to extract a watch history, but the 'content_id' column should be distinct and ordered in decending order from most recently watched... I'm finding it difficult to explain, but basically exactly how the YouTube watch history works (no multiple instances of the same video).

This is what I have so far:

SELECT content_id, time_stamp, user_id,
COUNT(DISTINCT content_id) AS hit_count
FROM watch_history
WHERE user_id = X
GROUP BY content_id
ORDER BY time_stamp DESC

It works, but I need the 'time_stamp' field to reflect the last time that content was watched, any help?


Answer Source

You have columns in the SELECT that are not in the GROUP BY. This is tolerably ok for user_id, because it is a fixed value. But for timestamp it is not ok.

Use the MAX() aggregation function:

SELECT content_id, MAX(time_stamp) as time_stamp, user_id, 
       COUNT(*) AS hit_count 
FROM watch_history 
WHERE user_id = X 
GROUP BY content_id, user_id
ORDER BY MAX(time_stamp) DESC;

Note: COUNT(DISTINCT content_id) doesn't make sense. It will return "1" on each row (unless there is one row where content_id is NULL). I think you just want COUNT(*).