Missing Messages Report

[Steven]

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.

[Wilson Logan]

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.

[pdellera]

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/. It's an interesting read.

For the db3 or pg4 sqlite databases, I found this variation works:

Code Select Expand

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:

Code Select Expand

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.

[Steven]

Cool! A nested sub-query. I have not seen one of those since I retired. :-)