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

select id, cat_name from 

(
 select @rank:=if(@prev_cat=cat_name,@rank+1,1) as rank,
         id,cat_name,@prev_cat:=cat_name
from Table1,(select @rank:=0, @prev_cat:="")t
   order by cat_name, id desc
) temp

where temp.rank<=2

You may verify result at http://sqlfiddle.com/#!9/acd1b/7

Answer 2

Well, it's pretty ugly, but it looks like it works.

select
cat_name,
max(id) as maxid
from
table1
group by cat_name
union all
select
cat_name,
max(id) as maxid
from
table1
where not exists
(select 
  cat_name,
  maxid
  from 
  (select cat_name,max(id) as maxid from table1  group by cat_name) t
  where t.cat_name = table1.cat_name and t.maxid = table1.id)
group by cat_name
order by cat_name

SQLFiddle

Basically, it takes the max for each cat_name, and then unions that to a second query that excludes the actual max id for each cat_name, allowing you to get the second largest id for each. Hopefully all that made sense...

Answer 3

please try this, it worked in the sample data given

SELECT `id`, `category`.`cat_name`
FROM `info`
LEFT JOIN `category` ON `info`.`cat_id` = `category`.`cat_id`
WHERE `category`.`cat_name` IS NOT NULL and (SELECT count(*) 
FROM info t
WHERE t.id>=info.id and t.cat_id=category.cat_id )<3

GROUP BY `category`.`cat_name`,id
ORDER BY `category`.`cat_name` ASC

Answer 4

Better to use rank function the below sample query for your ouput will be helpful check it

select a.* from 
(
select a, b ,rank() over(partition by b order by a desc) as rank
from a
group by b,a) a

where rank<=2

Answer 5

I have written query for you. I hope it will resolve your problem :

SELECT 
    id, cat_name
FROM
    (SELECT 
        *,
            @prevcat,
            CASE
                WHEN cat_name != @prevcat THEN @rownum:=0
            END,
            @rownum:=@rownum + 1 AS cnt,
            @prevcat:=cat_name
    FROM
        category
    CROSS JOIN (SELECT @rownum:=0, @prevcat:='') r
    ORDER BY cat_name ASC , id DESC) AS t
WHERE
    t.cnt <= 2;

Answer 6

Very simple Group By ID. it is group duplicate data

Answer 7

the only thing you need is adding limit option to the end of your query and ordering in descending order as shown below:

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` DESC
LIMIT 2 

Answer 8

Your query is like Select id, cat_name from mytable group by cat_name then update it to Select SELECT SUBSTRING_INDEX(group_concat(id), ',', 2), cat_name from mytable group by cat_name and you will get output like as follows

id  cat_name
1,2   Cat-1
3,5   Cat-2

Does it helps?

Answer 9

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    |
+----+----------+

Answer 10

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.

Answer 11

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 ;