To allow me to shuffle business order of rows via my web page, I have
column with row sequence. In some cases, like after deletes and
reorganizing, I want to resequence those numbers replacing gaps and
dups with new numbers.

for this example, say my table looks like this:

CREATE TABLE [dbo].[Seq](
[Name] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Seq] [int] NULL
) ON [PRIMARY]

GO




say my data looks like this after some operation:

dad 1
mom 2
brother 4
sister 5
cousin 5

I'd like for it so then look like this:

dad 1
mom 2
brother 3
sister 4
cousin 5

How?

Thank you for any help or information.

[quoted text, click to view]


One quick way to do this is to drop the column and Recreate the Column
with Identity (Seed =1).

thanks
-Mahesh

[quoted text, click to view]

Not if you expect the sequence of Seq after the update to match the sequence
before. Also, an IDENTITY column may have gaps in the sequence.

Jobs,
Your table doesn't have a key! What is supposed to define the ordering of
Seq when duplicate values exist before the update? Adding a key would
obviously help and without it I don't think there's necessarily a good
answer to your question. Try:

WITH t (Name, Seq, NewSeq) AS
(SELECT Name, Seq, ROW_NUMBER() OVER (ORDER BY Seq, Name DESC)
FROM Seq)
UPDATE t SET Seq = NewSeq;

Hope this helps.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

[quoted text, click to view]

very nice!

Say name is a unique key - It appears the natural order in which rows
are inserted works fine for resolving dups. Originally I thought
duplicate seq entries would not ever happen . This does however expose
another question. I need a way of inserting between rows and need the
inserted row to fall infront of the row I inserted on. It does appear
that inserting with the prior seq reordering results in the desired
effect.

say I've got this
select * from seq order by seq

Name Seq
-------------------- -----------
mom 1
dad 2
brother 3


insert seq (name,seq)values ('billybob',2) (with the hope wedging a
row between dad and brother)

results look like this momentarily (which is of course not the desired
effect):

select * from seq order by seq

Name Seq
-------------------- -----------
mom 1
billybob 2
dad 2
brother 3

so we reorganize with your code:

WITH t (Name, Seq, NewSeq) AS
(SELECT Name, Seq, ROW_NUMBER() OVER (ORDER BY Seq, Name DESC)
FROM Seq)
UPDATE t SET Seq = NewSeq;

and now results now look like this.

Name Seq
-------------------- -----------
mom 1
dad 2
billybob 3
brother 4


Correct me if Im wrong. If rows are inserted with the same seq number,
they will naturally order in sequence of entry right? So on that last
data, If I want to insert something between billybob and dad, I just
insert with billybob's seq minus 1 and reorganize.

If so and it appears so that's Great!
My plan is to use this code to allow the users to reorder and insert
steps (rows) using an asp.net gridview with push up, push down and
insert command buttons.

very nice.

[quoted text, click to view]

Wrong. There is no such thing as a natural ordering of a table because
tables are unordered sets by definition. That's why keys are important. The
only way to preserve the insertion order is to have a column or columns to
record that information for you. Note that my UPDATE statement derived its
result based on Seq and Name. So if you want to base it on insertion order
you might change it to:

WITH t (Name, Seq, CreatedDt, NewSeq) AS
(SELECT Name, Seq, CreatedDt, ROW_NUMBER() OVER (ORDER BY Seq, CreatedDt
DESC)
FROM Seq)
UPDATE t SET Seq = NewSeq;

that's assuming you add a CreatedDt column to record the insertion order.

On the other hand, if you just want to insert each new row at a specific
place in the sequence it might be easier to do this:

UPDATE Seq
SET seq = seq +1
WHERE seq >= @Seq ;

INSERT INTO Seq (Name, Seq)
VALUES (@Name, @Seq);

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--