Svein Erling Tysvær wrote:

>
> I'd expect
>
> select first 1 d1.Word_No, d2.Word_No, d3.Word_No,
> d4.Word_No, d5.Word_No, d6.Word_No
> from DownLoad d1
> join DownLoad d2 on d1.Used = d2.Used
> and d1.Word_No +1 = d2.Word_No
> join DownLoad d3 on d1.Used = d3.Used
> and d1.Word_No +2 = d3.Word_No
> join DownLoad d4 on d1.Used = d4.Used
> and d1.Word_No +3 = d4.Word_No
> join DownLoad d5 on d1.Used = d5.Used
> and d1.Word_No +4 = d5.Word_No
> join DownLoad d6 on d1.Used = d6.Used
> and d1.Word_No +5 = d6.Word_No
> where d1.Used = 'F'
> order by 1
>
> to perform reasonably decently if Word_No is indexed

That's exactly the query I was thinking of. In a
stored procedure, if the sequence is something like this

1, 1, 1, 2, 2, 3, 4, 6, 7, 9, 9, 9, 9, 11, 12, 13, 14, 15, 16

the sql query is going to try ever number until it gets to
11 and succeeds. A stored procedure would notice the hole
after 4 and start again at the next higher value (6), find
the hole at 8, try the next higher value (9), fail again,
and finally succeed with 11. That four searches instead of
14.

Cheers,

Ann
next higher value,