mysql select statement result limit

I have query:

select id, name from categories where parent开发者_JAVA技巧_id in (
    select id 
    from categories 
    where top_main_place > 0
)

it selects child info (outer select) of their parent nodes (inner select)

the problem is: I don't need to have all child nodes data, maximum 6 child nodes data per parent id

how can I reach this result?

btw, sorry for my poor english

---

You should order the outer query by parent_id, give each row a row number (resetting the row number whenever parent_id changes) and then filter out any row with row number greater than 6, check out this question for example and sql code.

---

This page shows how to limit the number of results returned by a query, in several dialects of SQL: http://www.w3schools.com/sql/sql_top.asp

---

select id, name FROM (
    select id, name, IF(@parent_id=parent_id, @count:=@count+1, @count:=0) as count, @parent_id:=parent_id from categories, (select @count:=0, @parent_id:=0) as x where parent_id in (
        select id 
        from categories 
        where top_main_place > 0
    ) order by parent_id
) y where count <= 6;