By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE DATAA(id INT, room INT, type VARCHAR(20), created_at INT);
INSERT INTO DATAA VALUES(8214, 83, 'msg', 1571726466),
(8213, 83, 'msg', 1571724983),
(8212, 83, 'ad', 1571724982),
(8211, 83, 'msg', 1571724978),
(8210, 83, 'msg', 1570861659),
(8209, 83, 'msg', 1570861656),
(8208, 83, 'msg', 1570861650),
(8207, 83, 'ad', 1570861643);
SELECT * FROM DATAA
| id | room | type | created_at |
|---|---|---|---|
| 8214 | 83 | msg | 1571726466 |
| 8213 | 83 | msg | 1571724983 |
| 8212 | 83 | ad | 1571724982 |
| 8211 | 83 | msg | 1571724978 |
| 8210 | 83 | msg | 1570861659 |
| 8209 | 83 | msg | 1570861656 |
| 8208 | 83 | msg | 1570861650 |
| 8207 | 83 | ad | 1570861643 |
SELECT MAX(T1.id), T1.room, type, MAX(created_at), COUNT(*) count_msg
FROM DATAA T1
JOIN (SELECT MAX(id) id, room
FROM DATAA
WHERE type = 'ad'
GROUP BY room) T2 ON T1.id > T2.id AND T1.room = T2.room
GROUP BY room, type
| MAX(T1.id) | room | type | MAX(created_at) | count_msg |
|---|---|---|---|---|
| 8214 | 83 | msg | 1571726466 | 2 |