On 02/03/2019 16:50, Lester Caine les...@lsces.co.uk [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
