On 3/2/19 11:06 AM, Lester Caine les...@lsces.co.uk [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
