How to set limit for joined mysql table?

Question

I'm trying to combine data from 3 tables with below query

SELECT `media_category`.id as cat_id,
        `media_category`.category_name as cat_name,
        `video`.`id` as vid_id,
        `video`.`name` as vid_name,
        `screenshots`.name as screenshot_name
        FROM `media_category`
        LEFT JOIN video
        ON `media_category`.id = `video`.`category_id`
        LEFT JOIN screenshots
        ON `video`.id = `screenshots`.`media_id`
        WHERE `screenshots`.name NOT LIKE '%\_%'

version: mysql 5.7

It workes well. But I need to limit the rows getting from video table to LIMIT 10 per category

Any idea for that?

What is the expected outcome? 10 videos per category, or 10 videos overall? — josh.trow, Apr 28 '20 at 19:41
you find your answer here https://stackoverflow.com/questions/2129693/using-limit-within-group-by-to-get-n-results-per-group — nbk, Apr 28 '20 at 19:47
Your left outer join is not working properly, since you have a predicate in the `WHERE` clause. They are silently converted to INNER joins. — The Impaler, Apr 28 '20 at 19:57

score 1 · Answer 1 · answered Apr 28 '20 at 20:19

You don't mention which MySQL version you are using so I'll assume it's MySQL 8.x.

In MySQL 8.x you can use the DENSE_RANK() function to identify the rows you want. Then a simple predicate will remove the ones you don't want.

For example, if we want to limit to 2 rows of b on each a (irrespective of rows in c), you can do:

select *
from (
  select
    a.id,
    b.id as bid,
    dense_rank() over(partition by a.id order by b.id) as drank,
    c.id as cid
  from a
  left join b on b.aid = a.id
  left join c on c.bid = b.id
) x
where drank <= 2

Result:

id  bid  drank     cid
--  ---  -----  ------
 1   11      1     100
 1   11      1     101
 1   11      1     102
 1   11      1     103
 1   12      2     120
 2   20      1     200
 2   20      1     201
 2   21      2     202
 3   30      1  <null>

As you can see it shows only 11 and 12 for the id = 1, even though there are 5 total rows for it (all five are of rank 1 and 2). You can see the running example at DB Fiddle. The data script for this example is:

create table a (id int primary key not null);

insert into a (id) values (1), (2), (3);

create table b (id int primary key not null, aid int references a (id));

insert into b (id, aid) values
  (11, 1), (12, 1), (13, 1), (14, 1),
  (20, 2), (21, 2),
  (30, 3);

create table c (id int primary key not null, bid int references b (id));

insert into c (id, bid) values
  (100, 11), (101, 11), (102, 11), (103, 11),
  (120, 12), (130, 13), (140, 14),
  (200, 20), (201, 20),
  (202, 21);

I changed the question tag to MySQL-5.7. Unfortunately that's quite of a different animal. As you say, this solution won't work there. — The Impaler, Apr 28 '20 at 20:32

How to set limit for joined mysql table?

1 Answers1