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;