How to using group by in strict mode correct?

Question

I have the table for messages like this:

CREATE TABLE `message` (
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `from_id` bigint(20) unsigned NOT NULL,
    `to_id` bigint(20) unsigned NOT NULL,
    `body` text COLLATE utf8mb4_unicode_ci NOT NULL,
    `status` tinyint(4) NOT NULL,
    `created_at` timestamp NULL DEFAULT NULL,  
    PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

and try to select each last message from userX to userY, but mysql always says that I have nonaggregated columns (without only_full_group_by its work well). How I can select this in strict mode? Not working query for example :

select
     t1.created_at,
     t1.from_id,
     t1.to_id,
     t1.body,
     t1.status,
     ( select created_at from test1.message where from_id = t1.from_id order by created_at desc limit 1 ) as last_timestamp

from test1.message as t1
group by t1.from_id 
having t1.created_at = last_timestamp

There is no aggregation here so you should not be using group by. — P.Salmon, Jan 24 '20 at 09:56
You typically `GROUP BY` the same columns as you `SELECT`, except those who are arguments to set functions. — jarlh, Jan 24 '20 at 10:04

score 0 · Answer 1 · answered Jan 24 '20 at 09:55

Don't aggregate - instead, filter.

One option uses a correlated subquery that computes the maximum created_at per user:

 select m.*
 from message m
 where m.created_at = (
      select max(m1.created_at) from message m1 where m1.from_id = m.from_id
)

You can also use the anti-left join pattern:

select m.*
from message m
left join message m1 on m1.from_id = m.from_id and m1.createt_at > m.created_at
where m1.id is null

This phrases as: get the records from which no other record exists with the same from_id and a greater created_at.

score 0 · Answer 2 · answered Jan 24 '20 at 09:58

You don't even need to be using GROUP BY here, and without it your current query should actually be valid:

SELECT
    t1.created_at,
    t1.from_id,
    t1.to_id,
    t1.body,
    t1.status,
    (SELECT t2.created_at FROM test1.message t2
     WHERE t2.from_id = t1.from_id
     ORDER BY t2.created_at DESC LIMIT 1) AS last_timestamp
FROM test1.message AS t1
HAVING t1.created_at = last_timestamp;

MySQL has overloaded the HAVING operator to be usable in place of a WHERE clause, with the additional feature that aliases defined in the SELECT clause can actually be used there.

How to using group by in strict mode correct?

2 Answers2