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



  1. I follow your blog for quite a long time and must tell that your posts are always valuable to readers.

    Comment by Vince Delmonte — April 14, 2009 @ 8:56 pm

  2. Did you see Simons phizog on Brtiatn got genius when Susan Boyle stareted to carol? That was a eye-openerer for every one! I v seen it hundred times already! lol

    Comment by guapyfuffWeet — April 22, 2009 @ 12:54 am

  3. ok depressing if the mortify in the diabolical digs but did you see Susan Boyle chorus? OMG!!! still SIMON was in eye-opener! This lady is amazing! God glorify her!!

    Comment by tereLatspaump — April 22, 2009 @ 8:24 pm

  4. music download search mp3 unashamed, mp3 treble charger dowload the road to sourceville mp3 switchfoot mp3 download free still lives perry blake mp3 songs sukhbir mp3 free downlaod scott bradley tom and jerry

    Comment by Telpunlippete — May 7, 2009 @ 2:50 pm

  5. I was just reading a question on how to persuade your down comforter all unimportant again and practically all the answers said that you can save some tennis balls in the dryer to amount to that happen. how to serve a tennis ballSo how does that plough exactly? Why does this happen? Can you use something else quite than tennis balls? Gratefulness you!
    how tennis heed swarms

    Comment by Sorbidokilk — May 19, 2009 @ 6:36 am

  6. Говорят что в Ельцу бобы по яйцу

    Comment by dolbik — May 19, 2009 @ 1:28 pm

  7. buy virginia slims ultra sunlight cigarettes
    tawdry doral cigarette for a song doral cigarette|base doral cigarettes
    cut-price doral cigarettes tuppenny doral cigarettes
    for peanuts gpc cigarette tuppenny gpc cigarettes
    tuppenny gpc cigarettes cheap gpc cigarettes
    virginia slims ultra

    Comment by Remybrercub — May 25, 2009 @ 4:13 pm

  8. Slightly embarrassed and with a quick smile to the bsu driver, she reached behind her to unzip her skirt a little, thinkng that this would give hre enough slack to raise her leg.

    Comment by AcurceAutheri — July 11, 2009 @ 12:58 am

  9. If you love watching football, then you probably also out of to wager on the gamble, and mainly on your favorite teams?

    Comment by nflliga — July 10, 2010 @ 10:11 pm

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

Create a free website or blog at

%d bloggers like this: