How to select two records of a certain category from mysql database?

Question

MySQL

there is a table with goods.

You need to select the first two records of each category with one query,
i.e. in the end it should turn out

PS If possible in yii2 format, if not, a simple mysql query will also go, I'll figure it out there =)

Answer 1

Select t1.cat_id, t1.id, t2.* from 
(Select cat_id, min(id) from table group by cat_id
UNION 
Select cat_id, min(id) from table
where id not in (Select min(id) from table group by cat_id) group by cat_id) t1
left join table t2 on t1.id = t2.id

MSSQL but I don't think it will be much different

Answer 2

Several options .

Answer 3

There are at least 6 solutions in 1 query, see Select multiple records from each group