CODEWITHSUNDEEP
mysqlsql
Eghbal Shirasb
Eghbal Shirasb

Reputation: 3

limit in group by in mysql

I had a question about mysql. Suppose I have a table as follows. database img

In this table, I have 3 parameters: user, video and favorite_speed.

Now the question is, I want to get the average speed of the last 3 videos seen by each user in each course. To connect the video to the course, we must have 2 joins between the course and the topic and the video.

For example, with the following code, I get the average speed of each user in a course. But the question is how do I get the average of the last 3 videos?

SELECT avg(favorite_speed),favorite_speed.user_id,topics.course_id 
FROM favorite_speed,videos,topics 
WHERE favorite_speed.video_id=videos.id AND topics.id=videos.topic_id 
GROUP BY user_id,videos.topic_id  
ORDER BY `avg(favorite_speed)`  DESC

Upvotes: 0

Views: 62

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Now the question is, I want to get the average speed of the last 3 videos seen by each user in each course.

You can use a window function to identify the most recent three videos. Assuming it is based on favorite_speed.id:

SELECT fs.user_id, v.course_id, avg(fs.favorite_speed), 
FROM (SELECT fs.user_id, fs.favorite_speed, v.course_id,
             ROW_NUMBER() OVER (PARTITON BY fs.user_id, v.course_id ORDER BY fs.id DESC) as seqnum
      FROM favorite_speed fs JOIN
           videos v 
           ON fs.video_id = v.id 
     ) fsv
WHERE seqnum <= 3
GROUP BY fs.user_id, v.course_id; 
ORDER BY avg(fs.favorite_speed) DESC;

Note that the topics table is not needed because the information is in the videos table.

Upvotes: 1

Related Questions