SQL sequent grouped rows

+------+------+------+------------+
| 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 |
| 8206 |   83 | msg  | 1570861632 |
| 8255*|   82 | msg* | 1571732235 |
| 8254 |   82 | msg* | 1571732235 |
| 8253 |   82 | msg* | 1571732235 |
| 8252 |   82 | msg* | 1571732235 |
| 8251 |   82 | msg* | 1571732234 |
| 8250 |   82 | ad   | 1571732234 |
| 8249 |   82 | msg  | 1571732234 |
| 8248 |   82 | msg  | 1571732234 |
| 8247 |   82 | msg  | 1571732234 |
| 8246 |   82 | msg  | 1571732234 |
| 8245 |   82 | msg  | 1571732233 |
| 8244 |   82 | msg  | 1571732233 |
| 8243 |   82 | msg  | 1571732233 |
| 8242 |   82 | ad   | 1571732232 |
| 8241 |   82 | msg  | 1571732232 |
| 8240 |   82 | msg  | 1571732232 |
| 8239 |   82 | msg  | 1571732231 |
| 8238 |   82 | msg  | 1571732231 |
| 8237 |   82 | ad   | 1571732231 |
| 8236 |   82 | msg  | 1571732231 |
| 8235 |   82 | msg  | 1571732231 |
| 8234 |   82 | msg  | 1571732230 |
| 8233 |   82 | msg  | 1571732230 |
| 8232 |   82 | msg  | 1571732230 |
| 8231 |   82 | msg  | 1571732230 |
| 8230 |   82 | msg  | 1571732230 |
| 8229 |   82 | msg  | 1571732229 |
| 8228 |   82 | msg  | 1571732228 |
| 8227 |   82 | msg  | 1571732228 |
| 8226 |   82 | msg  | 1571732228 |
| 8225 |   82 | msg  | 1571732227 |
| 8224 |   82 | msg  | 1571732227 |
| 8223 |   82 | ad   | 1571732227 |
| 8222 |   82 | msg  | 1571732226 |
| 8221 |   82 | msg  | 1571732223 |
| 8220 |   82 | msg  | 1571732223 |

I have table of ‘messages’. There could be regular message or ad. Each message belongs to room. I need to get last messages by date in each room and in this selection must be count of each regular messages (type=msg) before first encounter of type ‘ad’ given that messages’ order are descending.

Expected will probably explain better what I need:

+------+------+------+------------+-----------+
| id   | room | type | created_at | count_msg |
+------+------+------+------------+-----------+
| 8214 |   83 | msg  | 1571726466 | 2         |
| 8255 |   82 | msg  | 1571732235 | 5         |
+------+------+------+------------+-----------+

In the source I marked with ‘*’ which rows should count/showup in the result.

PS. Sorry if grammar is wrong.

UPD: In case of same created_at I guess it should order by id desc as well.

Answer

From your expected output it seems you need all the msg rows after the last ad occurs. This an be easily achieved using a sub-query –

SELECT MAX(T1.id), T1.room, type, MAX(created_at), COUNT(*) count_msg
FROM YOUR_TABLE T1
JOIN (SELECT MAX(id) id, room
      FROM YOUR_TAB
      WHERE type = 'ad'
      GROUP BY room) T2 ON T1.id > T2.id AND T1.room = T2.room
GROUP BY room, type

Here is the fiddle.

Advertisement

Answer