March 8, 2007

Using LEAD to determine if number is between rows

Filed under: example, lead function, PL/SQL, pl/sql code — Michael Moore @ 8:40 pm

You can adapt this SQL to use any numeric colum from any table you have.

Notice the NVL funtion. We need this because there is no 'NEXT' number after
 the highest number, so a NULL is returned in that position.

here we are saying, "substitute the highest possible value if null is returned here". 
This will make our BETWEEN logic work correctly.

SELECT portal_key,
nvl(lead (portal_key) over ( order by portal_key),99999999) as nextkey
FROM tx_portal
WHERE 4000 between portal_key and nextkey;

portal_key is any numeric


Blog at