max_date_user_message.sql

-- 
-- There is a `t` table with 3 fields:
--   `uid` - user ID
--   `dt` - date and time of message
--   `s` - text of the message
--   Write an SQL query to retrieve date and text of the last message for all users.
  
-- schema
CREATE TABLE `user_message_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uuid` int(11) NOT NULL,
  `s` varchar(500) DEFAULT NULL,
  `dt` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4844 DEFAULT CHARSET=utf8;

insert into `user_message_log` (`uuid`, `s`, `dt`)
values (1, 'u1 error 1', 123);
insert into `user_message_log` (`uuid`, `s`, `dt`)
values (1, 'u1 error 2', 124);
insert into `user_message_log` (`uuid`, `s`, `dt`)
values (1, 'u1 error 3', 125);

insert into `user_message_log` (`uuid`, `s`, `dt`)
values (2, 'u2 error 1', 231);
insert into `user_message_log` (`uuid`, `s`, `dt`)
values (2, 'u2 error 2', 232);
insert into `user_message_log` (`uuid`, `s`, `dt`)
values (2, 'u2 error 3', 233);
insert into `user_message_log` (`uuid`, `s`, `dt`)
values (2, 'u2 error 4', 234);

insert into `user_message_log` (`uuid`, `s`, `dt`)
values (3, 'u3 error 1', 231);
insert into `user_message_log` (`uuid`, `s`, `dt`)
values (3, 'u3 error 2', 234);

--- query

select t1.dt, t1.s 
from `user_message_log` t1
left join `user_message_log` t2
on 
t1.uuid = t2.uuid and t1.dt < t2.dt
where t2.dt is null