MYSQL Group by column with 2 rows for each group

Question

I need 2 id for each group.

SELECT `id`, `category`.`cat_name` 
FROM `info`
LEFT JOIN `category` ON `info`.`cat_id` = `category`.`cat_id`
WHERE `category`.`cat_name` IS NOT NULL
GROUP BY `category`.`cat_name`
ORDER BY `category`.`cat_name` ASC 

How to do this?

Sample Data:

id  cat_name
1   Cat-1
2   Cat-1
3   Cat-2
4   Cat-1
5   Cat-2
6   Cat-1
7   Cat-2

Output Will be:

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

Answer 1

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 NULL
GROUP 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 NULL
GROUP BY c`.`cat_name`
ORDER BY c.`cat_name` ASC ;