Monday, November 24, 2014

compare current row with previous / next row in a table

There must be a numeric identity / Unique column for comparing current with next / previous Rows in a table. 

Lets say you want to compare both Previous and next rows with current row in ItemMaster Table with unique numeric columns ItemID:

Select Cur.ItemName [Curent Value],
IsNull(Pre.ItemName,0) [Previous Value],
IsNull(Nex.ItemName,0) [Next Value]
From ItemMaster AS Cur
LEFT JOIN ItemMaster AS Pre On Pre.ItemID = Cur.ItemID - 1
LEFT JOIN ItemMaster AS Nex On Nex.ItemID = Cur.ItemID + 1

No comments:

Post a Comment