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
WHERE user_id = X
GROUP BY content_id
ORDER BY time_stamp DESC
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.
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;
COUNT(DISTINCT content_id) doesn't make sense. It will return "1" on each row (unless there is one row where
NULL). I think you just want