CODEWITHSUNDEEP
mysqldatabasesortinggrouping
user2597933
user2597933

Reputation: 63

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?

Thank.

Upvotes: 0

Views: 50

Answers (2)

Charles Bretana
Charles Bretana

Reputation: 146469

do you want the hits sorted by most recent view of the Content, or by most recent view of the content by a specific viewer?

If the answer is the former, then:

SELECT content_id, user_id, 
     COUNT(content_id) AS hit_count 
FROM watch_history h
WHERE user_id = X 
GROUP BY content_id
ORDER BY (Select Max(timestamp) from watch_history 
          Where user_id = X 
             and content_id = h.content_id) DESC

or

SELECT content_id, time_stamp, user_id, 
     COUNT(content_id) AS hit_count 
FROM watch_history h
   join watch_history last
       on last.content_id = h.Content_id
          and last.timestamp = 
              (Select max(timestamp) FROM watch_history 
               where user_id = X 
                  and content_id = h.content_id)
WHERE user_id = X 
GROUP BY content_id, time_stamp
ORDER BY last.timestamp desc

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269643

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(*).

Upvotes: 3

Related Questions