Group results only if a condition is met in mysql-CodePudding

Is there a way to group results only if the user id and month creation date are the same?

user_id	rank	created_at
1	3	2021-12-06 14:11:14
1	1	2021-11-06 14:11:14
1	2	2021-12-06 14:11:14

SELECT
    *
FROM
    table_name
GROUP BY
    user_id,
    DATE_FORMAT(created_at, '%m-%y')
ORDER BY
    rank DESC

With the query what I get is as follows

user_id	rank	created_at
1	3	2021-12-06 14:11:14

and what I need is to obtain the following result:

Because the records are from different months: 2021-12-06 14:11:14 - 2021-11-06 14:11:14

user_id	rank	created_at
1	3	2021-12-06 14:11:14
1	1	2021-11-06 14:11:14

CodePudding user response：

You seem to want the highest rank per user per month so a max(rank) would be appropriate , also group_by without aggregate functions is not appropriate.

DROP table if exists t;

create table t
(user_id int,   rnk int,    created_at datetime);
insert into t values
(1, 3   ,'2021-12-06 14:11:14'),
(1, 1   ,'2021-11-06 14:11:14'),
(1, 2   ,'2021-12-06 14:11:14');

sELECT
    t.user_id,max(t.rnk) `rank` ,DATE_FORMAT(created_at, '%m-%y') ym
FROM
    t
GROUP BY
    user_id,
    DATE_FORMAT(created_at, '%m-%y')
ORDER BY
    `rank` DESC

 --------- ------ ------- 
| user_id | rank | ym    |
 --------- ------ ------- 
|       1 |    3 | 12-21 |
|       1 |    1 | 11-21 |
 --------- ------ ------- 
2 rows in set (0.001 sec)

CodePudding user response：

Below Query Products the output that you required.

SELECT
    user_id,rank,
    created_at
FROM
    table_name
GROUP BY
    user_id,
    created_at
ORDER BY
    rank DESC

https://www.db-fiddle.com/f/aBgB6QdMGe3ZmX65u9mXFC/1