On 3/2/19 11:06 AM, Lester Caine lester@... [firebird-support]
wrote:

>  
>
> I think I will probably have to add some extra logic into this problem,
> but it SHOULD be simple enough ...
>
> Table with three fields - Gallery_No, Image_No, Item_position
>
> A gallery can consist of any number of images but we normally only
> display a selection of up to 30. Item_position is a FLOAT which allows
> several styles ordering, with one style 'page.pos' so one can group
> pictures to a variable length page. The bottom line here is that
> 'Item_position' is not sequential ... and that is the rub. I now need to
> identify the record before and the record after for a library system
> where each gallery is a collection of pdf articles and the client would
> like to be able to navigate back and forward through each volume.
>
> In the past ( 20 years ago ;) ) I would simply have added a second
> position column which is consecutive numbers and reordered them when
> changes are made, but I'm wondering if with all the things that have
> been added since then has provided an SQL means of returning the before
> and after value directly from the current data?
>
> --
> Lester Caine - G8HFL
>

I would think one way to get the previous item from a give one would be
a select on items with Item_position less than the current
Item_position, in decreasing order, with a limit of 1 item (and the
reverse for the one after it). If there is an index on Item_position,
that should be a fairly efficient query.

--
Richard Damon