On 02/03/2019 16:50, Lester Caine lester@... [firebird-support]
wrote:

>> OK another reason for moving over to FB3 ...
>> LEAD and LAG working nicely ...
> Except it's only half the solution:(
> I was hoping to avoid traversing the parent gallery ... it would be nice
> to just return a single record with the target content id.

OK got an encapsulated solution so don't need to download the whole tree

WITH TREE AS
( SELECT ITEM_CONTENT_ID,
LAG( ITEM_CONTENT_ID) OVER (ORDER BY ITEM_POSITION) AS PRE,
LEAD( ITEM_CONTENT_ID ) OVER (ORDER BY ITEM_POSITION) AS POST
FROM FISHEYE_GALLERY_IMAGE_MAP
WHERE GALLERY_CONTENT_ID = :parent_id
ORDER BY ITEM_POSITION )
SELECT PR.PRE, PR.POST FROM TREE PR
WHERE PR.ITEM_CONTENT_ID = :content_id

I think the next step is to also generate the :parent_id in the query
but I now need to migrate the production site to FB3 so I can use what I
already have :(

--
Lester Caine - G8HFL
-----------------------------
Contact - https://lsces.co.uk/wiki/?page=contact
L.S.Caine Electronic Services - https://lsces.co.uk
EnquirySolve - https://enquirysolve.com/
Model Engineers Digital Workshop - https://medw.co.uk
Rainbow Digital Media - https://rainbowdigitalmedia.co.uk