Join с LIMIT внутри

Здравствуйте.

У меня есть таблица с музыкальными группами (groups) и жанрами (genres). Для связи групп и жанров используется таблица genres_groups у которой поля group_id, genre_id

Задача: для нескольких жанров (их id я знаю) выбрать по 4 самых популярных группы. Популярность определяется столбцом avrating.
Если бы мне нужна была одна группа, то я бы сделал так:

Код (Text):

1. SELECT gg.genre_id, g . *
2. FROM genres_groups AS gg
3. LEFT JOIN (
4.    SELECT *
5.    FROM  `groups`
6.    ORDER BY avrating DESC
7. )g ON g.id = gg.group_id
8. WHERE gg.genre_id IN ( 1, 3, 6, 7, 10 )
9. GROUP BY gg.genre_id

Как же мне поставить LIMIT на мои группы? Такой вариант по понятным причинам не работает:

Код (Text):

1. SELECT gg.genre_id, g . *
2. FROM genres_groups AS gg
3. LEFT JOIN (
4.    SELECT *
5.    FROM  `groups`
6.    ORDER BY avrating DESC
7.    LIMIT 4
8. ) g ON g.id = gg.group_id
9. WHERE gg.genre_id IN ( 1, 3, 6, 7, 10 )

 

Долго еще сегодня пробовал разные варианты, единственное к чему пришел это к мысли, что должен быть способ сделать join к моим genre_id вот такого запроса, который выбирает 4 последних в пределах одного жанра

Код (Text):

1.  SELECT * FROM groups as gj
2.     LEFT JOIN genres_groups as ggj ON ggj.group_id = gj.`id`
3.     WHERE ggj.genre_id = 1
4.     ORDER BY gj.avrating DESC
5.     LIMIT 4

только вместо цифры 1, чтобы был тот genre_id к которому выполняется join. Реально ли вообще такое?

 

Да я находил в интернете такой вариант решения, но у меня id для группировки и сами данные в разных таблицах, а не в одной и я застопорился вот на таком:

Код (Text):

1. SELECT g.*,gg.*,
2.   if(@typex=gg.genre_id,
3.      @rownum:=@rownum+1,
4.      @rownum:=1+least(0,@typex:=gg.genre_id)) AS rown
5. FROM groups as g, genres_groups as gg, (SELECT @rownum:=1, @typex:=0) zz
6. WHERE gg.group_id = g.id
7. ORDER BY gg.genre_id, g.avrating DESC

Неправильно он проставляет номера строчек почему-то.

Дампы таблиц дам чуть позже.

 

Дампы будут, проблема актуальна, у меня просто доступ к базе только из дома, а домой последние дни очень поздно прихожу, не хватает сил из базы выбрать нужные данные. Спасибо, что помните об этом

 

вот feddly с маленьким объемом данных
http://sqlfiddle.com/#!2/8de75/2

а вот sql дампы с 8000 строк если вдруг надо будет быстродействие проверить или еще что
https://dl.dropboxusercontent.com/u/13843662/groups_temp.sql
https://dl.dropboxusercontent.com/u/13843662/genres_groups.sql
заранее спасибо за помощь

p.s. в таблице groups поле id называется index ;-) надеюсь, что это никого не запутает. Просто 5 лет назад, когда я придумывал таблицу я не знал что такое index на самом деле, а в универе преподавательница по sql именно так называла primary key поля. Так оно и осталось с тех времен смущать всех своей созвучностью с индексами в базах данных.

я тоже иногда так делаю, но тут случай, когда я понимаю что с чем должно быть связано, но на ум не приходит ни одно стандартное средство, чтобы это реализовать. Тут нужна какая-то хитрость вроде работы с переменными, как в моем примере выше...

 

Мой косяк. Человеческий фактор. В fiddle пришлось их вручную набивать, так как данные из таблиц были слишком большие, а просто удалить из них часть было нельзя, так как тогда бы у некоторых групп не оказалось бы связей.

так и есть

Спасибо болшое за запрос, в fiddle работает, но выдает пустоту на полной базе. Я дописал инициализацию переменных и всё заработало. Непонятно почему где-то работает без этого, а где-то нет. Может быть версия mysql разная. Вот итоговый вариант, мало ли кому пригодится:

Код (Text):

1.  
2. SELECT genre_id, rn, group_id, avrating
3. FROM
4.   (
5.    SELECT x.group_id, x.avrating,
6.      (@rn := IF(@prev != x.genre_id, 1, @rn + 1)) AS rn,
7.      (@prev := x.genre_id) AS genre_id
8.    FROM
9.      (SELECT gg.genre_id, gg.group_id, g.avrating FROM groups AS g INNER JOIN genres_groups AS gg ON gg.group_id = g.`index`) AS x, (SELECT @rn:=0,@prev:=0) as z
10.    ORDER BY x.genre_id, x.avrating DESC, x.group_id
11.   ) AS y
12. WHERE
13.   rn <= 4

Внутрь

Код (Text):

1.  
2. SELECT gg.genre_id, gg.group_id, g.avrating FROM groups AS g INNER JOIN genres_groups AS gg ON gg.group_id = g.`index`

еще можно дописать WHERE genre_id IN (1,3,5) или любые другие условия по вкусу. Запрос кстати быстро отрабатывает даже если никаких условий не ставить и выбирать для всех жанров и из всех групп.

artoodetoo, спасибо за помощь, воспользовался вашей подписью ;-)

Добавлено спустя 8 минут 22 секунды:

не совсем понял, что имеется ввиду. где-то пропущено "не".
группы действительно работают в разных жанрах, но рейтинг у них общий и формируется из голосов людей. Люди голосуют за то нравится ли им группа в целом, а не за конкретные достижения в определенных жанрах.