FacebookTwitterDiggDeliciousStumbleuponGoogle BookmarksRedditLinkedin

I found this as a very common question when people start in sql. You can use sub-queries but I wouldn't recommend that as its slower and not scalable. So.. I like the method I show you bellow:

Create a variable and save on it the row number(@num).
We also need another variable to save the "page" id as its necessary to restart the row count when a different page appears.
Finally we filter by @num with as much rows we want. Remember matchNO is a calculated field so you cant filter in a where statment, use having instead.

The code

set @num := 0, @parent := 0;
SELECT p.[yourFields],IF(@parent = p.id,@num:=@num+1,@num:=1) as matchNO, @parent:=p.id
	FROM parent p
		LEFT JOIN child c on c.p_id = p.id
			WHERE [yourFilters]
				having matchNO <= 3;


The row number is necessary to figure out in what row you are. The page variable tell you the page in the last row so you can compare and then set @num to 1 again when necesary.

Freedom Code

All existing content on this site can be used and modified to pleasure the developer who implements it. 

There are full and complete freedom to use the code. Developers can collaborate on this projects or recommend changes for improve it.