retrieve the next and the previous reference of a record

• Hema Mutreja

  Right there with Babe

  Points: 772

  More actions

  September 18, 2008 at 2:37 pm

  #193037

  Hi,

  I am using SQL Server 2000.

  I have a table with the columns Current_id, Name, Previous_id and Next_id.

  I want to update 2 columns of that table. Column Previous_id with the Current_id of the previous record and column next_id with the current_id of the next record.

  Is there a way that can be achieved?

  Thanks

• Garadin

  One Orange Chip

  Points: 29613

  More actions

  September 18, 2008 at 3:57 pm

  #873952

  What is your end goal here? There is probably a more efficient way to accomplish what you're asking. For example, tracking both the next and previous ID's per record is somewhat redundant, as next can be calculated by the previous, and won't be available at the point and time you're inserting the record anyways.

  Are you open to other ways of accomplishing this goal, or do you have to have it the way you asked for it?

  [Edit]

  I had time to kill before a meeting so here is the code to accomplish it as you asked for it.

  ----------------- CREATE SAMPLE DATA TABLE-----------------

  CREATE TABLE #Test(

  Itemvarchar(10),

  Identint)

  INSERT INTO #Test(Item, Ident)

  VALUES('Dog',1)

  INSERT INTO #Test(Item, Ident)

  VALUES('Cat',2)

  INSERT INTO #Test(Item, Ident)

  VALUES('Dog',3)

  INSERT INTO #Test(Item, Ident)

  VALUES('Bird',4)

  INSERT INTO #Test(Item, Ident)

  VALUES('Cat',5)

  INSERT INTO #Test(Item, Ident)

  VALUES('Cat',6)

  INSERT INTO #Test(Item, Ident)

  VALUES('Bird',7)

  INSERT INTO #Test(Item, Ident)

  VALUES('Dog',8)

  INSERT INTO #Test(Item, Ident)

  VALUES('Dog',9)

  INSERT INTO #Test(Item, Ident)

  VALUES('Dog',10)

  INSERT INTO #Test(Item, Ident)

  VALUES('Bird',11)

  INSERT INTO #Test(Item, Ident)

  VALUES('Dog',12)

  -----------------------------------------------------------

  ---------------- SEQUENCE DATA ----------------------------

  CREATE TABLE #1(

  ID1int identity(1,1),

  Itemvarchar(10),

  Identint)

  INSERT INTO #1(Item, Ident)

  SELECT Item, Ident

  FROM #Test

  ORDER BY Item, Ident

  -----------------------------------------------------------

  --------------- GEN NEXT/PREV -----------------------------

  SELECT T.Item,

  T.Ident,

  (SELECT MIN(Ident) FROM #1 S WHERE S.Item = T.Item AND S.ID1 > T.ID1 ) NextID

  INTO #2

  FROM #1 T

  ORDER BY T.Item, T.Ident

  SELECT T.Item,

  S.Ident PreviousIdent,

  T.Ident CurrentIdent,

  T.NextID NextIdent

  FROM #2 T LEFT JOIN #2 S ON T.Item = S.Item AND T.Ident = S.NextID

  -----------------------------------------------------------

  Seth Phelabaum

  ---

  Consistency is only a virtue if you're not a screwup. 😉

  Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]