Member Avatar for Acute

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
  • 117 Views
  • 9 Hours Discussion Span
  • Latest Post Latest Post by smantscheff
Member Avatar for smantscheff

Use a WHERE clause.

Member Avatar for shdwmage 
SELECT * FROM table [B]WHERE etype='p'[/B] LIMIT 0,10
Edited by shdwmage because: n/a
Member Avatar for Acute

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'.

Member Avatar for smantscheff

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 because: n/a
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.