--- In firebird-support@yahoogroups.com, "Ann W. Harrison" wrote:

> jackmills75 wrote:
> >>
> >> select distinct d1.Word_No
> >> from DownLoad d1
> >> join DownLoad d2 on d1.Used = d2.Used
> >> where ( abs( d1.Word_No - d2.Word_No ) = 1 ) AND
> >> ( d1.Used = 'F' )
> >> order by d1.Word_No
> >>
> >> -steve
> >
> > Thanks Steve
> >
> > The query filters out single results = 'F' but allows groups of 2 or
> > more, i.e. wont sort for groups of six or more
>
> 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