mysql - Find every 2 alternate records in sql -

mysql - Find every 2 alternate records in sql -

i have issue sql query. it's below.

in particular field record consists of "a" , "b" only.

now if want find 2 records of "a" followed 2 records of "b" , 1 time again 2 records of "a" , 2 records of "b" , on till end of records.

example output should below. id field --------- ----- 2 3 1 b 5 b 4 7 6 b 8 b ......... .........

.............. , on

kindly help me above....as stuck query.

thanks!

you can enumerating rows each value , cleverly ordering results:

select id, field (select t.*, if(field = 'a', @a_rn := @a_rn + 1, @b_rn := @b_rn + 1) rn table t (select @a_rn := 0, @b_rn := 0) vars ) t order (rn - 1) div 2, field;

mysql