MySQL: Limit on certain field

Question

Say I have a table with field like this:

ID       Name         Parent_ID
1       Maxim         1
2       Bruce         1
3       Jonas         3
4       Steve         4
5       Chloe         4
6       Paul          4
7       Frank         7
8       Paula         8
9       Martin        9
10      Hank          9

And I want to get a query with only top 3 different parent Ids, which would have Parent_ID of 1, 3, and 4 like below:

ID     Name         Parent_ID
1       Maxim         1
2       Bruce         1
3       Jonas         3
4       Steve         4
5       Chloe         4
6       Paul          4

How can I get this using MySQL. Can I use the LIMIT function. Can anyone help me?

Thanks.

Learn the use IN clause in MySql – Sabyasachi Mishra Jul 22 '15 at 08:17 — Sabyasachi Mishra, Jul 22 '15 at 08:17

wiretext · Accepted Answer · 2015-07-22T08:33:09.880

2

i hope this will help you

select t1.* from table1 as t1
inner join
(select distinct Parent_ID from table1 order by Parent_ID limit 3) as t2
on t1.Parent_ID = t2.Parent_ID

edited Jul 22 '15 at 08:33

answered Jul 22 '15 at 08:22

wiretext

3,302
14
19

i got this error when i run the query: #1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' – yodann Jul 22 '15 at 08:27

score -1 · Answer 2 · answered Jul 22 '15 at 08:19

-1

SELECT * FROM Tablename
-> WHERE parent_id IN ( 1,3,4 );

answered Jul 22 '15 at 08:19

Sabyasachi Mishra

1,677
2
31
49

may be i'm not making myself too clear. What I want actually is to limit the result like in the LIMIT clause, so I can get the result per page. – yodann Jul 22 '15 at 08:23

MySQL: Limit on certain field

2 Answers2