SELECT t.id, t.title_english, t.title_language, t.languageFROM ( SELECT a.id, a.title_english, a.title_language, a.language, @rn := IF(@prev = a.title_language, @rn + 1, 1) AS rn, @prev := a.title_language AS prev_tl FROM amethyst_1 a JOIN ( SELECT title_language FROM amethyst_1 WHERE title_language IS NOT NULL GROUP BY title_language HAVING COUNT(*) > 1 ORDER BY COUNT(*) DESC LIMIT 10000 -- top X duplicated titles ) top_dup ON a.title_language = top_dup.title_language CROSS JOIN (SELECT @rn := 0, @prev := '') vars ORDER BY a.title_language, a.id) AS tWHERE t.rn <= 5 -- first Y rows per duplicated titleORDER BY t.title_language, t.rn;