Cy on Nostr: Meh, I tested and it took an 2 extra seconds to index a blob over 10 million rows ...
Meh, I tested and it took an 2 extra seconds to index a blob over 10 million rows than two integer fields. Anyway this uh... works:
```with foo as (select id,author,updated, updated_ns,
count(id) over (partition by author order by updated desc, updated_ns desc
range between unbounded preceding and current row)
AS cyount from posts)
select * from foo where cyount <= 3 and author = 87 order by updated, updated_ns;
The question is whether it's slow as balls or not. Seem to recall window functions aren't particularly peppy in sqlite. Still, would it be slower than dozens of repeated queries for N posts from each author?
#sqlite #Programming
```with foo as (select id,author,updated, updated_ns,
count(id) over (partition by author order by updated desc, updated_ns desc
range between unbounded preceding and current row)
AS cyount from posts)
select * from foo where cyount <= 3 and author = 87 order by updated, updated_ns;
The question is whether it's slow as balls or not. Seem to recall window functions aren't particularly peppy in sqlite. Still, would it be slower than dozens of repeated queries for N posts from each author?
#sqlite #Programming