Mysql limit in each subquery

Question

I have a table with categories

Categories
id | title | visible
1  |  one  |    1
2  |  two  |  null
3  | three |    1

a table with photos from the categories above

Photos
id | category_id |     title      | visible
1  |      1      |  photo.one     |    1
2  |      1      |  photo.two     |  null
3  |      1      |  photo.three   |    1
4  |      1      |  photo.four    |    1
5  |      2      |  photo.five    |  null
6  |      2      |  photo.six     |    1
7  |      3      |  photo.seven   |    1
8  |      3      |  photo.eight   |    1
9  |      3      |  photo.nine    |    1

I need to take only 2 photos with photos.visible = 1 from every category with category.visible = 1.

I tried with limit but i can limit only all records, not per category.

The result has to be

Result
id | category_id |     title      | visible | category_title
1  |      1      |  photo.one     |    1    |      one
2  |      1      |  photo.three   |    1    |      one
3  |      3      |  photo.seven   |    1    |     three
4  |      3      |  photo.eight   |    1    |     three

Any help please?

And [this](http://stackoverflow.com/questions/2000744/mysql-limit-results-per-category) and more or less [this](http://stackoverflow.com/questions/16946784/average-of-latest-n-records-per-group/17001909#17001909). — GolezTrol, Sep 01 '14 at 16:26
i saw similar answers, but all of them are complex (additional variables / if case) - do i really need all of this to limit each subquery ? — mozg, Sep 01 '14 at 16:29
it is a duplicate, if you find a better answer post it to original question, not here. — Bulat, Sep 01 '14 at 16:41
if it was a complete dublicate to a post with a normal answer, i would not post it. — mozg, Sep 01 '14 at 16:46
Yes, it's a complete duplicate. You can do it with or without variables. With variables is typically faster, that's all. And i assume your photo ids are wrong. !?!? Also how can visible be NULL? — Strawberry, Sep 01 '14 at 17:23

score 0 · Answer 1 · answered Sep 01 '14 at 16:58

One way you can achieve this if there is only 2 photos you need but the solution will give you a single row per category and two photos name as comma separated list if this will be good enough for you then you can use group_concat to get all photos as comma separated list per category and substring_index to get 2 photos from the result of group_concat function

select c.*,
substring_index(group_concat(p.title),',',2) photos
from Categories c
join Photos p on (c.id = p.category_id and p.`visible` = 1)
where c.`visible` is not null
group by c.id

Result set will be like

ID  TITLE   VISIBLE PHOTOS
1   one     1       photo.one,photo.three
3   three   1       photo.eight,photo.nine

See demo

According to docs The result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024. The value can be set higher, although the effective maximum length of the return value is constrained by the value of max_allowed_packet.

thanks for the solution, but i really dont want to concat the results. the solution (almost working and question WHY does it not work) is here [stackoverflow](http://stackoverflow.com/questions/25615509/mysql-why-variable-is-not-changing-in-if) — mozg, Sep 02 '14 at 05:14

Mysql limit in each subquery

1 Answers1