limit in subquery mysql

Question

I am tying an alternate to limit sub queries in mysql, since mysql 5 does not support limit keyword in subquery:

SET @i = 0;

SELECT BuildID, COUNT(TestCase) 
  from results R 
 where R.BuildID IN (select B.BuildID 
                       from build B 
                      where ( @i := ( @i +1 ) ) <= 25 
                        and B.BuildID NOT LIKE 'Tx%' 
                   order by B.buildid desc) 
group by R.BuildID 
order by R.id desc;

but I am just getting one resultset. Cannot figure out why? If I execute the subquery separately, I get 6 BuildID's

Answer 1

Because of the grouping group by R.BuildID. If all the results have the same BuildID they will all be one row.

Or if the results table only have one row that has the BuildID that also exists in the subquery

Answer 2

From what I see, @i is never reset in the subquery. Let say you have 25 records for BuildID == 1. In this case @i == 25, and when BuildID changes to 2, (@i = @i+1) evaluates to 26, so you subquery always returns NULL for all BuildIDs except first (or few first BuildIDs, if you have less than 25 records per BuildID).

I'd say you need to change your query and get rid of subquery. I'm not sure if I understand exactly what you want, but I believe you want to get BuildID from results table for which in table build there are no more than 25 entries. I suppose that B.PK_COLUMN is a primary key in build table.

SELECT R.BuildID, COUNT(R.TestCase),Count(B.PK_COLUMN) as cnt2 
from results R
INNER JOIN from build B ON (B.BuildID = R.BuildID AND B.BuildID NOT LIKE 'Tx%')
GROUP BY R.BuildID
HAVING cnt2 <25;