Geek Answers Handbook

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 it?

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

The output will be:

 id cat_name 6 Cat-1 4 Cat-1 7 Cat-2 5 Cat-2
+7
sql mysql greatest-n-per-group group-by
source share
11 answers

If you need two arbitrary identifiers, 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 use LEFT JOIN and then aggregate the column in the second table. This is usually not a good idea, because non-matches are all placed in a NULL group. Also, your WHERE turns LEFT JOIN into INNER JOIN , so I fixed it. The WHERE may or may not be necessary, depending on whether cat_name NULL NULL .

If you want the two largest or smallest - and you can have them in one 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 ;
+3
source share
 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 on the Group-wise-max blog .

0
source share

In a database that supports window functions, you can list the position of each record in each group (ROW_NUMBER () OVER (PARTITION BY cat_name ORDER BY id id DESC)), and then select these records in relative positions 1 or 2.

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

This request

  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;

gives:

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

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

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

Does it help?

0
source share

you only need to add the limit option at the end of your request and ordering in descending , 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
0
source share

A very simple group by identifier. this is group repeating data

0
source share

I wrote you a request. I hope this solves 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;
0
source share

It is better to use the rank function, below an example query for your output it will be useful to 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
0
source share

please try this, it worked in the given sample data

 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
0
source share

Well, this is pretty ugly, but it seems 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 max for each cat_name, and then combines it with a second query, which excludes the actual maximum id for each cat_name, allowing you to get the second largest identifier for each. Hope this all made sense ...

0
source share
 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 can check the result at http://sqlfiddle.com/#!9/acd1b/7

0
source share

More articles:


All Articles