SQLite group by near times

Question

I have an SQLite table like this:

sqlite> select * from things order by id;
id          created         
----------  ----------------
1           2458171.46967479
2           2458171.46967747
3           2458171.46968049
4           2458171.46968362
5           2458171.46968647
6           2458171.46968948
7           2458171.46969561
8           2458171.46973709
9           2458171.46974006
10          2458171.46974368
11          2458171.46978387

created is a julianday timestamp. I'd like to select the most recent group of rows that were recorded around the same time as each other. "Around the same time" being something like "within 100ms of each other."

I know how to divide the whole table into discreet buckets, but I want something different than that. For example (and this doesn't match the table above), let's say that that most recent record has a timestamp of 0. The next most recent has a timestamp of +75 and the third most recent has a timestamp of +160.

Or in other words:

id     tstamp
------ -------
156    0
155    75
154    160

• If my threshold is 50, then only #156 should be returned.
• If my threshold is 75, then both #156 and #155 should be returned.
• If my threshold is 85, then ALL three should be returned, because 160 is within 85 of the next most recent row.

Any suggestions for how to proceed? I could do it in application code, but it will be much faster in SQL, if I can. I suspect I might need to do something with WITH RECURISIVE perhaps?

Answer 1

After reading the WITH RECURSIVE documentation a bunch: https://www.sqlite.org/lang_with.html

Here's my solution:

WITH RECURSIVE
    what(x) AS (
        SELECT max(created) FROM things
        UNION
        SELECT things.created FROM what, things
        WHERE things.created >= (what.x - 85)
    )
SELECT x FROM what ORDER BY 1;

And here's some sample queries showing that it works:

sqlite> select * from things;
id          created   
----------  ----------
1           160       
2           85        
3           0         

sqlite> WITH RECURSIVE
   ...>     what(x) AS (
   ...>         SELECT max(created) FROM things
   ...>         UNION
   ...>         SELECT things.created FROM what, things
   ...>         WHERE things.created >= (what.x - 50)
   ...>     )
   ...> SELECT x FROM what ORDER BY 1;
x         
----------
160       

sqlite> WITH RECURSIVE
   ...>     what(x) AS (
   ...>         SELECT max(created) FROM things
   ...>         UNION
   ...>         SELECT things.created FROM what, things
   ...>         WHERE things.created >= (what.x - 75)
   ...>     )
   ...> SELECT x FROM what ORDER BY 1;
x         
----------
85        
160       

sqlite> WITH RECURSIVE
   ...>     what(x) AS (
   ...>         SELECT max(created) FROM things
   ...>         UNION
   ...>         SELECT things.created FROM what, things
   ...>         WHERE things.created >= (what.x - 85)
   ...>     )
   ...> SELECT x FROM what ORDER BY 1;
x         
----------
0         
85        
160