0

Please login or register to vote for this query.

(click on this box to dismiss)

SO question http://stackoverflow.com/q/8360901/939860

Q&A about the site for researchers and developers who explore the principles of a system through analysis of its structure, function, and operation

-- Demo for VKarthik
-- SO question http://stackoverflow.com/q/8360901/939860

DECLARE @t TABLE(
 Id        INT PRIMARY KEY
,ItemId    INT NOT NULL
,SeqNumber INT NOT NULL);

INSERT INTO @t VALUES
 (1,100,254)
,(2,100,1)
,(3,200,3)
,(4,100,255)
,(5,200,5)
,(6,200,7)

;WITH b AS (
   SELECT *
         ,row_number() OVER (ORDER BY Id) AS rn
   FROM   @t
   ), x AS (
   SELECT 
       b.Id
      ,b.ItemId    AS prev_Itm
      ,b.SeqNumber AS prev_Seq
      ,c.ItemId    AS next_Itm
      ,c.SeqNumber AS next_Seq
   FROM   b
   LEFT   JOIN b c ON c.rn = b.rn + 1   -- next row
   WHERE  c.ItemId = b.ItemId
   AND    c.SeqNumber <> (b.SeqNumber + 1)%256
   )
SELECT Id, prev_Itm, 'Previous' AS PrevNext, prev_Seq
FROM   x
UNION  ALL 
SELECT Id, next_Itm ,'Next', next_Seq
FROM   x
ORDER  BY Id, PrevNext DESC

fork query

created dec 7 11 anonymous