Select top X records for each group or default

Question

I have the following schema:

users:

id email
1  'user.one@test.com'
2  'user.two@test.com'

video_group:

id title
1  'Group 1'
2  'Group 2'

videos:

id group_id rank title
1  1        1    'Group 1 - Video 1'
2  1        2    'Group 1 - Video 2'
3  2        1    'Group 2 - Video 1'

user_video_play_times:

video_id user_id time last_update
2        1       12   01-02-2018
1        1       120  01-01-2018

I need to get the time, user_id, video_id, and group_id of the last video played by a user in specific groups, but if there's no records on user_video_play_times for a group, the video with the lowest rank should be returned. For example:

user_id group_id video_id time
1       1        2        12    -- user.one + group 1
1       2        3        0     -- user one + group 2

This is the query I have so far:

SELECT
   pt.user_id user_id,
   v.id       video_id,
   g.id       group_id,
   pt.time    time
FROM
   videos v
   INNER JOIN video_groups g ON g.id = v.group_id
   LEFT JOIN user_video_play_times pt ON 
      pt.video_id = v.id AND 
      pt.user_id = 1
   LEFT JOIN (
      SELECT 
         g.id AS g_id,
         MAX(pt.last_update) AS pt_last_update
      FROM
         user_video_play_times pt
         INNER JOIN videos v ON v.id = pt.video_id
         INNER JOIN video_groups g ON g.id = v.group_id
      WHERE
         pt.user_id = 1 AND
         g.id IN (1, 2)
      GROUP BY
         g.id
   ) lpt ON lpt.g_id = g.id AND lpt.pt_last_update = pt.last_update
WHERE
   g.id IN (1, 2)
GROUP BY
   g.id

It is sort of working, but...

1. Adding v.title to the column selection messes the results for some reason, making everything return only videos with rank 1. Any idea why?
2. Could this query be optimized, or is there another slicker way to achieve the same results?

Any help with this is really appreciated!

DB fiddle here

Update 1:

This issue seems to only happen when the column os of type text.

Answer 1

Since your db<>fiddle is for MariaDB version 10.3; I am presuming that you have Window Functions available.

We can use Row_number() function over a partition of group_id to get row number values, as per the defined rules. Video with latest last_update value will have Row number of 1 and so on. If there is no video played, then the one with least value of Rank will have Row number = 1.

We can use this result-set as a Derived Table, and consider only those rows where Row number = 1.

SELECT 
  dt.user_id, 
  dt.group_id, 
  dt.video_id, 
  dt.video_title, 
  dt.time 
FROM 
(
  SELECT
     pt.user_id AS user_id,
     g.id       AS group_id,
     v.id       AS video_id,
     v.title    AS video_title,  
     pt.time    AS time,  
     ROW_NUMBER() OVER(PARTITION BY v.group_id 
                       ORDER BY pt.last_update DESC, 
                                v.`rank` ASC) AS row_num 
  FROM videos AS v
  INNER JOIN video_groups AS g 
    ON g.id = v.group_id AND 
       g.id IN (1,2) 
  LEFT JOIN user_video_play_times AS pt 
    ON pt.video_id = v.id AND 
       pt.user_id = 1 
) AS dt 
WHERE dt.row_num = 1

View on DB Fiddle

Result:

| user_id | group_id | video_id | video_title       | time |
| ------- | -------- | -------- | ----------------- | ---- |
| 1       | 1        | 2        | Group 1 - Video 2 | 12   |
|         | 2        | 3        | Group 2 - Video 1 |      |

---

PS: Note that Rank is a Reserved Keyword, and you should really avoid using it as column/table name.