Extending TSQL ROW_NUMBER to Find MAX Rows or Row Deltas

Posted by Adam Rink at 2/10/2009
Categories: Performance,Queries
Tags: Running Total ROW_Number Deltas
Some common queries in a database involve grabbing the last value entered into a table for a set of items or comparing a row to its previous row.  In the past this involved nasty sub queries one to find the max date and then use that value in outer query.  This did not provide the cleanest of code or the best performance.  Using the ROW_NUMBER function allows us to simplfy the code and improve performance.

Let's make a CTE first off our query to simplify our code:

WITH cteTranLog (Item, LogValue, MaxDateNum, Date)
AS 
(    SELECT   Item,	   
              LogValue,
              MaxDateNum = ROW_NUMBER() OVER (PARTITION BY Item ORDER BY Date DESC),
              Date    
     FROM dbo.TransactionLog    
     WHERE ProcessID = 514	
)
To grab the last values entered in a table:

SELECT Item, LogValue, Date
FROM cteTranLog
WHERE MaxDateNum = 1  -- we grab the top row only since we are sorted by max date on top
Notice there is only one table seek or scan.  A subquery to grab the max date for each item would involve 2 seeks or scans of the table.

The other query involves using row number to look at the current row.  This can be handy with running totals or finding deltas between rows. 

SELECT cur.Item, cur.LogValue, cur.Date, prev.Date, prev.LogValueFROM cteTranLog cur	
INNER JOIN cteTranLog prev ON (prev.MaxDateNum - 1) = cur.MaxDateNum	-- using a -1 because of sort order of desc, dates are descending so 1 is the current making a lesser date 2




 

What did you think of this article?




Trackbacks
Trackback specific URL for this entry
  • No trackbacks exist for this entry.
Comments
Display comments as (Linear | Threaded)
Page: 1 of 3
  • 7/27/2009 Andy White wrote:
    Exactly what I was looking for. Thanks a lot!!!
    Reply to this
    1. 6/30/2010 coogi jeans wrote:
      tiffanyrings
      silk scarves
      rhinestoneearrings
      rhinestoneearrings
      pandorabeads
      cartierjewelry
      humanhairwigs
      pandora beads
      nike tn
      Reply to this
    2. 7/20/2010 Gas can spout wrote:
      I havent any word to appreciate this post
      Reply to this
  • 8/31/2009 twfxhnax wrote:
    obyjwyfz http://oitapzfj.com pxquoxhe wopnalji
    Reply to this
  • 9/2/2009 cheap meridia wrote:
    It is common sense to take a method and try it. If it fails, admit it frankly and try another. But above all, try something.
    Reply to this
    1. 3/18/2010 Zend CMS wrote:
      Nice post.I like the way you start and then conclude your thoughts. Thanks for this information .I really appreciate your work, keep it up
      Reply to this
      1. 3/18/2010 Business Network Kent wrote:
        I havent any word to appreciate this post.....Really i am impressed from this post....the person who create this post it was a great human..thanks for shared this with us.
        Reply to this
      2. 3/18/2010 IT support kent wrote:
        Excellent read, I just passed this onto a colleague who was doing a little research on that. And he actually bought me lunch because I found it for him smile So let me rephrase that.
        Reply to this
    2. 3/18/2010 Dedicated Servers UK wrote:
      I really appreciate the kind of topics you post here. Thanks for sharing us a great information that is actually helpful. Good day!
      Reply to this
    3. 3/18/2010 Video Marketing Services wrote:
      I just read through the entire article of yours and it was quite good. This is a great article thanks for sharing this informative information. I will visit your blog regularly for some latest post.
      Reply to this
    4. 3/18/2010 Peterhelmore007 wrote:
      It’s really great post, nice blog..I would like to appreciate your work and would like to tell to my friends.
      Thanks for sharing
      Business Network Kent | IT support kent
      Reply to this
    5. 9/1/2010 electric dog fencing wrote:
      Thanks for sharing the program.
      Reply to this
  • 9/2/2009 argilla wrote:
    You're dealing with the demon of external validation. You can't beat external validation. You want to know why? Because it feels sooo good.
    Reply to this
  • 9/2/2009 prozac wrote:
    Each body has its art...
    Reply to this
  • 9/2/2009 buy meridia wrote:
    Always seek out the seed of triumph in every adversity.
    Reply to this
  • 9/2/2009 bupropion wrote:
    Life is divided into the horrible and the miserable.
    Reply to this
  • 9/3/2009 lisinopril wrote:
    If a dog jumps in your lap, it is because he is fond of you; but if a cat does the same thing, it is because your lap is warmer.
    Reply to this
    1. 7/19/2010 Wireless Dog Fence wrote:
      even i have a pet dog when i go to home after a long time,my puppy insists me to lift him,really the dogs are active and sportive...
      Reply to this
  • 9/3/2009 orthostatic wrote:
    All programmers are playwrights and all computers are lousy actors.
    Reply to this
  • 9/3/2009 cheap xenical wrote:
    Intimacy is what makes a marriage, not a ceremony, not a piece of paper from the state.
    Reply to this
  • 9/3/2009 azithromycin wrote:
    When you have got an elephant by the hind leg, and he is trying to run away, it's best to let him run.
    Reply to this
  • 9/3/2009 sildenafil wrote:
    I'm moving, but don't worry! [Someone once] told me we're all on the same planet, so I'll be okay!
    Reply to this
  • 9/3/2009 prilosec wrote:
    I have never been especially impressed by the heroics of people who are convinced they are about to change the world. I am more awed by those who struggle to make one small difference after another.
    Reply to this
  • 9/3/2009 fennel wrote:
    Rest is the sweet sauce of labor.
    Reply to this
  • 9/3/2009 rambunctious wrote:
    The cloning of humans is on most of the lists of things to worry about from Science, along with behaviour control, genetic engineering, transplanted heads, computer poetry and the unrestrained growth of plastic flowers.
    Reply to this
  • 9/3/2009 witty wrote:
    Know, first, who you are; and then adorn yourself accordingly.
    Reply to this
  • 9/3/2009 ado wrote:
    Feet, why do I need them if I have wings to fly?
    Reply to this
  • 9/3/2009 augmentin wrote:
    Sometime they'll give a war and nobody will come.
    Reply to this
  • 9/3/2009 neurontin wrote:
    One of the keys to happiness is a bad memory.
    Reply to this
  • 9/3/2009 zoloft wrote:
    To lengthen thy life, lessen thy meals.
    Reply to this
  • 9/4/2009 purchase hydrocodone wrote:
    It's not the hours you put in your work that counts, it's the work you put in the hours.
    Reply to this
  • 9/4/2009 motrin wrote:
    Even if you're on the right track, you'll get run over if you just sit there.
    Reply to this
  • 9/4/2009 agteozux wrote:
    teophgfp http://ysesflxt.com rekmhwoy cocumath
    Reply to this
  • 9/4/2009 lexapro wrote:
    To do anything truly worth doing, I must not stand back shivering and thinking of the cold and danger, but jump in with gusto and scramble through as well as I can.
    Reply to this
    1. 7/12/2010 Adult Dating Online wrote:
      That's good idea.....
      Reply to this
  • 9/4/2009 casuarina wrote:
    Everything of importance has been said before by somebody who did not discover it.
    Reply to this
  • 9/4/2009 photoactivation wrote:
    Don't ever take a fence down until you know the reason it was put up.
    Reply to this
  • 9/5/2009 overfilling wrote:
    I love my past. I love my present. I'm not ashamed of what I've had, and I'm not sad because I have it no longer.
    Reply to this
  • 9/5/2009 buy prozac wrote:
    We can be sure that the greatest hope for maintaining equilibrium in the face of any situation rests within ourselves.
    Reply to this
  • 9/5/2009 zithromax wrote:
    The dead might as well try to speak to the living as the old to the young.
    Reply to this
  • 9/5/2009 boffinry wrote:
    All programmers are playwrights and all computers are lousy actors.
    Reply to this

Page: 1 of 3
Leave a comment

 Enter the above security code (required)

 Name

 Email (will not be published)

Your comment is 0 characters limited to 3000 characters.