Using LIMIT with SUBQUERY

$squerystring="";
SUBQUERY is a powerful to extract a subset of data from a set but there is a restriction for the usage of LIMIT. Database users are not unknown to the function of "LIMIT" in queries. It extracts the data range with a starting and ending position.

e.g "SELECT * FROM <tablename> order by <columnname> LIMIT 0,5"
Which means to fetch the first five records starting from position 0.

This statement can be used in SUBQUERY as

SELECT <columnname> FROM <tablename> where <columnname1> in ("SELECT <columnname3> from <tablename2> order by <columnname3> LIMIT 5")

The above Query gives error:

<span style="font-weight:bold;">#1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
</span>

To resolve the above error the SUBQUERY i.e query within parenthesis above is first executed and the output from it is stored into String and the String is concatenated in forthcoming query.

Soln: in PHP

$query1= SELECT <columnname3> from <tablename2> order by <columnname3> LIMIT 5
$squerystring="";
$result_squery=mysql_query($squery);
if(!$result_squery)
die(mysql_error());
$num_rows=mysql_num_rows($result_squery);
for($i=0;$i<=$num_rows;$i++)
{
$sqrow=mysql_fetch_array($result_squery,MYSQL_BOTH);
$squerystring.=$sqrow["tg_id"].",";
}
$squerystring= substr($squerystring, 0, (strlen($squerystring)-2));

Now the final Query becomes:

SELECT * FROM <tablename1> WHERE <columnname1> in(".$squerystring.") order by <columnname1> LIMIT 10