0

I have a column "etype" which stores enum('r', 'p').

So with each selection I want to extend the row number until the number of etype->'p' will reach some number, for instance 10.

If I use LIMIT 10, the total number of rows will be 10, but there is no guarantee that all of them in the field 'etype' will be 'p'. But I want exactly 10 'etype'->'p's, in this case the number of total rows is unknown.

Thnx in advance

3
Contributors
4
Replies
5
Views
6 Years
Discussion Span
Last Post by smantscheff
0
SELECT * FROM table [B]WHERE etype='p'[/B] LIMIT 0,10

Edited by shdwmage: n/a

0

where etype='p' will give me only 'p' rows', but I want both of them ('p', 'r'), but with exact number (10) of etype->'p'.
IN THIS CASE THE TOTAL NUMBER OF ROWS WILL BE 10 + number of rows with value of etype - 'r'.

0

You cannot do that with MySQL without a sort order.
If you have a sort order, e.g. by a field named ID, you can check the limit entry like this:

select t1.id from mytable t1 where (select count(*) from mytable t2 where etype='p' and t2.id < t1.id) = 9;

And instead of a limit clause you use this value as your select condition:

select * from mytable t where t.id <= (select t1.id from mytable t1 where (select count(*) from mytable t2 where etype='p' and t2.id < t1.id) = 9);

Edited by smantscheff: n/a

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.