MYSQL Group by column with 2 rows for each group

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

---

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 ;

---

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; 

在Group-wise-max博客中进行更多讨论。

---

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 <= 2
ORDER BY cat_name, id DESC;

produces:

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

链接地址: http://www.djcxy.com/p/28398.html

上一篇: 在树中查找所有最长的唯一路径

下一篇: MYSQL Group按列分组，每行2行