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 2
Page: 1 of 2
Leave a comment

 Enter the above security code (required)

 Name

 Email (will not be published)

 Website

Your comment is 0 characters limited to 3000 characters.