SQLite Forum

Novice question concerning adding conditional incremental integer counter
Login
The Window Function is probably more efficient.  

In both cases you will need the appropriate indexes.  (book_no, chapter_no, verse_no, index_no, strongs_no) for the Window Function, and (strongs_no) if you want to add the null values union.

You will also need the (book_no ...) index for your query as well.

If you really want the maximum speed for the Window Function )with the union) you probably want indexes thus:

```
create unique index xx1 on x (book_no, chapter_no, verse_no, index_no) where strongs_no != 'punc2';
create index xx2 on x (strongs_no) where strongs_no == 'punc2';
```