Group By, IFNULL and Order By date

Question

I have to take the rows of my table grouped by types, if they are not null, and user. Order them by time. But the grouping must be separated...

This is my table:

id	event_code	user	time
10	A	6	2021-10-20 09:59:00
9	null	6	2021-10-20 09:56:00
8	null	6	2021-10-20 09:54:00
7	A	6	2021-10-20 09:53:00
6	A	6	2021-10-20 09:52:00
5	null	6	2021-10-20 09:51:00
4	null	4	2021-10-20 09:50:00
3	B	6	2021-10-20 09:49:00
2	B	6	2021-10-20 09:48:00
1	B	12	2021-10-20 09:43:00

i want to get this:

id	event_code	user	time	number
10	A	6	2021-10-20 09:55:00	1
9	null	6	2021-10-20 09:54:00	1
8	null	6	2021-10-20 09:54:00	1
7,6	A	6	2021-10-20 09:53:00	2
5	null	6	2021-10-20 09:51:00	1
4	null	4	2021-10-20 09:50:00	1
3,2	B	6	2021-10-20 09:49:00	2
1	B	12	2021-10-20 09:43:00	1

score 1 · Answer 1 · answered Oct 20 '21 at 15:32

I have found this solution:

SELECT GROUP_CONCAT(id),event_code,user,time FROM(
SELECT 
 id,event_code,user,time,
if(@a = event_code, @b, @b := @b + 1) as grouping_col,
@a := event_code
FROM testing
JOIN (select @a := 1, @b := 0) as temp
  ) as t
GROUP BY grouping_col,user

http://sqlfiddle.com/#!9/abfcd4/17

But, how I order the grouped rows by time? I will have 7,6 but i have 6,7.

score 1 · Answer 2 · answered Oct 21 '21 at 09:50

@gasmor to keep the order you should include the order statement in the group_concat clause more on :https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html#function_group-concat, like:

    SELECT GROUP_CONCAT( id order by `time` desc),event_code,user,time 
FROM ( SELECT 
              id,
              event_code,
              `user`,
              `time`,
               if(@a = event_code, @b, @b := @b + 1) as grouping_col,
               @a := event_code
       FROM testing
JOIN ( 
      select @a := 1, @b := 0) as temp
      ) as t
GROUP BY grouping_col,user ;

Demo: https://www.db-fiddle.com/f/vhqJXYFy52xRtVBc97R1EL/4

You should keep in mind, when you use group by:

The selected columns should be part of the group by or part of an aggregation function like SUMN(), MAX()...

Group By, IFNULL and Order By date

2 Answers2