MYSQL Group by column with 2 rows for each group

If you need two arbitrary ids, then use min() and max():

SELECT c.`cat_name` , min(id), max(id)FROM `info` i INNER JOIN     `category` c     ON i.`cat_id` = c.`cat_id`WHERE c.`cat_name` IS NOT NULLGROUP BY c`.`cat_name`ORDER BY c.`cat_name` ASC ;

Note: You are using a LEFT JOIN and then aggregating by a column in the second table. This is usually not a good idea, because non-matches are all placed in a NULL group. Furthermore, your WHERE clause turns the LEFT JOIN to an INNER JOIN anyway, so I've fixed that. The WHERE clause may or may not be necessary, depending on whether or not cat_name is ever NULL.

If you want the two biggest or smallest -- and can bear to have them in the same column:

SELECT c.`cat_name`,       substring_index(group_concat id order by id), ',', 2) as ids_2 FROM `info` i INNER JOIN     `category` c     ON i.`cat_id` = c.`cat_id`WHERE c.`cat_name` IS NOT NULLGROUP BY c`.`cat_name`ORDER BY c.`cat_name` ASC ;

SELECT  id, cat_name    FROM        ( SELECT  @prev := '', @n := 0 ) init    JOIN        ( SELECT  @n := if(c.cat_name != @prev, 1, @n + 1) AS n,                @prev := c.cat_name,                c.cat_name,                i.id            FROM  `info`            LEFT JOIN  `category` ON i.`cat_id` = c.`cat_id`            ORDER BY  c.cat_name ASC, i.id DESC       ) x    WHERE  n <= 2    ORDER BY  cat_name ASC, id DESC; 

More discussion in Group-wise-max blog.

In a database that supported window functions, you could enumerate the position of each record in each group (ROW_NUMBER() OVER (PARTITION BY cat_name ORDER BY id DESC)) and then select those records in relative position 1 or 2.

In MySQL, you can mimic this by a self-join which counts the number of records whose id is greater-than-or-equal-to a record of the same cat_name (PARTITION ... ORDER BY id DESC). Record #1 in a cat_name group has only one record of >= its id, and record #N has N such records.

This query

 SELECT id, cat_name   FROM (   SELECT c.id, c.cat_name, COUNT(1) AS relative_position_in_group              FROM category c         LEFT JOIN category others                ON c.cat_name = others.cat_name                   AND                   c.id <= others.id          GROUP BY 1, 2) d   WHERE relative_position_in_group <= 2ORDER BY cat_name, id DESC;

produces:

+----+----------+| id | cat_name |+----+----------+|  6 | Cat-1    ||  4 | Cat-1    ||  7 | Cat-2    ||  5 | Cat-2    |+----+----------+