mysql - How to get top n records group by wildcards -

here table example:

+--------+-------------+ | id     | city_detail | +--------+-------------+ | 1      | 12_hyd_test | | 2      | 13_blr_test | | 3      | 15_blr_test | | 4      | 18_hyd_test | | 5      | 17_coi_test | | 6      | 22_coi_test | | 7      | 62_hyd_test | | 8      | 72_blr_test | | 9      | 92_blr_test | | 10     | 42_hyd_test | | 11     | 21_coi_test | | 12     | 82_coi_test | +--------+-------+-----+ 

from table, how use condition group select

+--------+-------------+ | id     | city_detail | +--------+-------------+ | 12     | 82_coi_test | | 11     | 21_coi_test | | 10     | 42_hyd_test | | 7      | 62_hyd_test | | 9      | 92_blr_test | | 8      | 72_blr_test | +--------+-------+-----+ 

in each city show 2 result (%coi% or %hyd% or '%blr%') order id desc

probably simplest method use variables:

select e.* (select e.*,              (@rn := if(@c = substr(city_detail, 4), @rn + 1,                         if(@c := substr(city_detail, 4), 1, 1                        )              ) seqnum       example e cross join            (select @c := '', @rn := 0) params       order substr(city_detail, 4)      ) e rn <= 2;