mysql - Limit on maximum two rows per column value -

is there way in mysql adapt query:

select * table order date desc limit 4 

so each product appears @ twice?

date | product | description   2015 | | alpha   2012 | | bravo   2014 | | charlie   2011 | b | delta   2000 | c | echo   1999 | d | foxtrot 1972 | d | g...   

the query should return:

2015 | | alpha   2014 | | charlie   2011 | b | delta   2000 | c | echo   

instead of

2015 | | alpha   2014 | | charlie   2012 | | bravo 2011 | b | delta   

you can run query this:

select thedate, product, description (   select t.*   , (@r:=case when @p=t.product @r+1 else 1 end) rownumber   , (@p:=t.product) theproduct   (select * thetable order product, thedate desc) t,        (select @r:=0,@p:='') r ) q rownumber <= 2 

(note i'm using thedate column name , thetable table name avoid having escape names).

select @r:=0,@p:='' joined query create 2 variables: @r store row number, , @p last product. if product same last row increment row number, otherwise reset 1.

this simulates row_number() on partition functionality available in sql server not in mysql. assigns number each row in each group (where group set of rows product), , clause limits results first 2 row numbers each group shown.