quote:

---

Originally posted by sakets_2000

how do you find the next row ? Is there a datetime or identity field for this ?

---

quote:

---

Originally posted by TG

It doesn't have to be an identity but you do need some indication in the table data to define the sequence of the rows. It could be a date column. If you just add an identity column now there is not guarantee that the values (generated for existing data) will correspond to the sequence they were added to the table.

Be One with the Optimizer
TG

---

-- Script for the table my solutions runs on.
-- The ID column must be sequential in the order inserted
CREATE TABLE [dbo].[FlagPlusMinus_v2] (
	[ID] int IDENTITY(1, 1) NOT NULL,
	[Price] float NULL,
	[Diff] float NULL,
	[Flag] nchar(1) NULL
)

-- Reset data for testing
update Sandbox.dbo.FlagPlusMinus_v2
set Diff = null, Flag = null
where ID > 1
-- Assign the first row with defaults
update Sandbox.dbo.FlagPlusMinus_v2
set Diff = 0.0, Flag = '-'
where ID = 1

-- ********* Solutions begins **********
-- This first pass updates the Diff column. It also updates
-- the Flag column where Diff > 0 or Diff < 0.
update Sandbox.dbo.FlagPlusMinus_v2
 set Diff = results.rDiff,
 	Flag = results.rFlag
from
	(select
		t1.ID rID,
		(t1.Price - t2.Price) 'rDiff',
		rFlag = case
			when (t1.Price - t2.Price) > 0 then	'A'
			when (t1.Price - t2.Price) < 0 then	'B'
			else null
		end
	from
		FlagPlusMinus_v2 t1,
		FlagPlusMinus_v2 t2
	where
		t1.ID = (t2.ID + 1)
	) results
where
	ID = results.rID

-- This is where it gets ugly.
-- Loops through data until all Flag values assigned.
-- What happens is that the row's Flag column, following a row where its
--    Flag is not null, is updated with the previous Flag value.
while (select count(ID) from FlagPlusMinus_v2 where Flag is null) > 0 begin
	update Sandbox.dbo.FlagPlusMinus_v2
	set Flag = results.rFlag
	from
		(select
			t1.ID rId,
			rFlag = t2.Flag
		from
			FlagPlusMinus_v2 t1,
			FlagPlusMinus_v2 t2
		where
			t1.ID = (t2.ID + 1)
			and t1.Diff = 0.0
			and t1.Flag is null
		) results
	where
		ID = results.rID
		and Flag is null
end

quote:

---

Originally posted by dbonneau
What if I use row_number() to assign number ?

select row_number () over (order by date, time) as sqnum

Thanks!

---

quote:

---

Originally posted by TG

quote:

---

Originally posted by dbonneau
What if I use row_number() to assign number ?

select row_number () over (order by date, time) as sqnum

Thanks!

---

Do your rows get inserted one by one or several at a time? if of properly sequencing the existing data.

---

quote:

---

Originally posted by sakets_2000

dbonneau, I remember seeing in your original post an identity column. You later edited it and removed that column. It had values like a1,a2,a3...
Do you have that??

---

quote:

---

Originally posted by sakets_2000

Yes. Your best bet is having that identity field come from the source. To TG's point, I think bulk insert will keep the order, but I haven't seen this documented anywhere.

---