How to get the sentence number for each words in words table in SQL?

Question

I have a a table like:

Id     Word
---    ----
1      this
2      is
3      a
4      cat.
5      that
6      is
7      a
8      dog.
9      and
10     so
11     on

and need to add a new column for sentence number base on dot character:

Id     Word     S#
---    ----     --
1      this     1
2      is       1
3      a        1
4      cat.     1
5      that     2
6      is       2
7      a        2
8      dog.     2
9      and      3
10     so       3
11     on       3

what is the best solution from the performance aspect??

Answer 1

select table.id, table.word, count(*) + 1 as serial_number
from table left join
( select id, word from table where word like '%.' ) Z 
on table.id > Z.id
group by table.id, table.word