I am assisting the owners of a couple of Yahoo groups & would like to save the Missing Messages Report to a text file that I can include in the archive I am sending them. For one of the groups a simple screenshot will do because there are not that many missing messages but for another group there 154 messages which does not work well with a screenshot.
It appears the databases are stored in an SQLite database. Does anyone have an SQL query that they could provide to generate this information.
select start, stop from (
select m.id + 1 as start,
(select min(id) - 1 from sequence as x where x.id > m.id) as stop
from sequence as m
left outer join sequence as r on m.id = r.id - 1
where r.id is null
) as x
where stop is not null;
Where 'm.id' is 'group_message.id'
Cheers,
Wilson.
Cheers,
Wilson.
I too had Googled and found that query at https://www.xaprb.com/blog/2005/12/06/find-missing-numbers-in-a-sequence-with-sql/ (https://www.xaprb.com/blog/2005/12/06/find-missing-numbers-in-a-sequence-with-sql/). It's an interesting read.
For the db3 or pg4 sqlite databases, I found this variation works:
select start, stop from (
select m.number + 1 as start,
(select min(number) - 1 from group_message as x where x.number > m.number) as stop
from group_message as m
left outer join group_message as r on m.number = r.number - 1
where r.number is null
) as x
where stop is not null
For an imported MySql dump, this works:
select start, stop from (
select m.number + 1 as start,
(select min(number) - 1 from messages as x where x.number > m.number) as stop
from messages as m
left outer join messages as r on m.number = r.number - 1
where r.number is null
) as x
where stop is not null;
Note that the only shortfall of the query is that it doesn't pick up missing sequences starting at 1. In my case, messages 1-4 inclusive are missing, and message 6 is missing, but it reports message 6 as being the first missing message.
Cool! A nested sub-query. I have not seen one of those since I retired. :-)