--- In firebird-support@yahoogroups.com, Svein Erling Tysvær
<svein.erling.tysvaer@...> wrote:

>
> > You could expand that to a string of six values but it would
> > be much less efficient than an equivalent stored procedure.
> >
> >
> > Regards,
> >
> > Ann
>
> 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 and there

aren't

> too many duplicates in this field. Of course, I'd expect d1 to go
> NATURAL, but the others should be able to utilise this index

(assuming

> 'Used' = 'F' isn't selective).
>
> Though getting each Word_No on a separate record is more difficult,
> excepting a stored procedure I cannot think of any other solution

than

> using UNION (though that may be due to me not having written a

single

> line of SQL for the last month) and that may make the query a lot

less

> readable and multiply the execution time by six (if I guess

correctly).

>
> HTH,
> Set
>

Ann Svein

There are no duplicates in the Word_No field, but I should have said
n consecutive rows (rather than 6) where Used = 'F'
It looks like the best way will be to use a SP.

Thank you for the help

Regards
Jack