/*  
extracted from "Advanced MySQL user variable techniques"  
(http://www.xaprb.com/blog/2006/12/15/advanced-mysql-user-variable-techniques/) 
*/ 
 
CREATE TABLE fruits ( 
  `type` varchar(10) NOT NULL, 
  variety varchar(20) NOT NULL, 
  price decimal(5,2) NOT NULL default 0, 
  PRIMARY KEY  (`type`,variety) 
) ENGINE=MyISAM DEFAULT CHARSET=latin1; 
 
insert into fruits(`type`, variety, price) values 
('apple',  'gala',       2.79), 
('apple',  'fuji',       0.24), 
('apple',  'limbertwig', 2.87), 
('orange', 'valencia',   3.59), 
('orange', 'navel',      9.36), 
('pear',   'bradford',   6.05), 
('pear',   'bartlett',   2.14), 
('cherry', 'bing',       2.55), 
('cherry', 'chelan',     6.33); 
 
 
set @num := 0, @type := ''; 
 
select `type`, variety, price, @num 
from fruits 
where 2 >= greatest( 
   @num := if(@type = `type`, @num + 1, 1), 
   least(0, length(@type := `type`))); 

------------------------------------------

Source Code

-----------------------------------------

<pre class="mysql">
/* 
extracted from "Advanced MySQL user variable techniques" 
(http://www.xaprb.com/blog/2006/12/15/advanced-mysql-user-variable-techniques/)
*/
 
CREATE TABLE fruits (
  `type` varchar(10) NOT NULL,
  variety varchar(20) NOT NULL,
  price decimal(5,2) NOT NULL default 0,
  PRIMARY KEY  (`type`,variety)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
 
insert into fruits(`type`, variety, price) values
('apple',  'gala',       2.79),
('apple',  'fuji',       0.24),
('apple',  'limbertwig', 2.87),
('orange', 'valencia',   3.59),
('orange', 'navel',      9.36),
('pear',   'bradford',   6.05),
('pear',   'bartlett',   2.14),
('cherry', 'bing',       2.55),
('cherry', 'chelan',     6.33);
 
 
set @num := 0, @type := '';
 
select `type`, variety, price, @num
from fruits
where 2 >= greatest(
   @num := if(@type = `type`, @num + 1, 1),
   least(0, length(@type := `type`)));
</pre>