Reorder my rows

Howdie y'all,

Im adding rows to my database like this:

GroupID = Identity
GroupUser = UserId
GroupColumn = 1
GroupPosition = Count (*) where UserId = UserId AND Column = 1

Int this way my table looks like this on a certain moment:

1 ; 1 ; 1 ; 0
2 ; 1 ; 1 ; 1
3 ; 1 ; 1 ; 2
4 ; 1 ; 1 ; 3

But then i would, for example, like to delete the 3rd row and i like to renumber the last column so form this:
1 ; 1 ; 1 ; 0
2 ; 1 ; 1 ; 1
4 ; 1 ; 1 ; 3 <- this is my problem

to this:
1 ; 1 ; 1 ; 0
2 ; 1 ; 1 ; 1
4 ; 1 ; 1 ; 2 <- here it is solved

Whats the simplest sql stored procedure to perform this reordering task?

Thanks,

Wes

Why not use the identity column to generate the 'position' at runtime?
SELECT
GroupID,
GroupUser,
GroupColumn,
COUNT(*) AS Position
FROM YourTable y1
JOIN YourTable y2 ON y2.GroupID <= y1.GroupID
GROUP BY
GroupID,
GroupUser,
GroupColumn
ORDER BY
Position
|||

Thanks you for your fast reply!

Some people would like to have group number 1 on the 3rd place or order them by groupname(not mentioned in the previous post)... they can do so by moving them up or down by changing the position or by clicking the order by name button...

So it might look like this before renumbering the position:
1 ; 1 ; 1 ; 0
4 ; 1 ; 1 ; 1
2 ; 1 ; 1 ; 3

So I really would like to renumber the Position field to cut out the 'empty' rows...

Can I loop through each record in an sql procedure?(sorry I'm such a newbie on sql-server... I've worked with small MSAccess databases only!!!!)

Thanks for thinking along with me!

Wesley

|||

I think I'm getting there...... I just get one ANNOYING ERROR! With this:

CREATE PROCEDURE [dbo].[spRenumberUserColumnGroups]
@.GroupColumn TINYINT,
@.GroupUserId INT
AS
UPDATE tblGroups
SET GroupPosition = ((SELECT COUNT(*) FROM tblGroups gr2 WHERE gr2.GroupPosition <= GroupPosition AND gr2.GroupUserId = @.GroupUserId AND gr2.GroupColumn = @.GroupColumn)-1)
WHERE GroupUserId = @.GroupUserId AND GroupColumn = @.GroupColumn
ORDER BY GroupPosition;
GO

I get this: Incorrect syntax near the keyword 'order'

WHY??? I'm starting to cry simply because I don't have a clue whats wrong with this ORDER BY. GroupName or GroupId all gives me the same nasty error. The moment I drop the ORDER BY it compiles just fine!!!!

I HATE SQL SERVER!!! Please help me...

Cheers,

Wes

|||You can't order an UPDATE. Simple as that. There is noguarantee what order the rows will be updated in. I haven'tstudied your code in any detail, so I don't know if you're depending onthat. If so, don't!
Also, think about concurrency -- what happens if two people removedifferent rows at the same time? Will this still result in acorrect answer? Make sure to think about that -- there may besome traps you haven't thought of.
|||

Thanks all for thinking along... after a long search and combining several examples into one I got to this and for know it works like a charm.

CREATE PROCEDURE [dbo].[spRenumberGroups]
@.GroupColumn TINYINT,
@.GroupUserId INT
AS
CREATE TABLE #RowNumber (
RowNumber int IDENTITY (0, 1),
GroupId int )

INSERT #RowNumber (GroupId)
SELECT GroupId
FROM tblGroups
WHERE GroupUserId = @.GroupUserId AND GroupColumn = @.GroupColumn
ORDER BY GroupPosition

UPDATE tblGroups
SET GroupPosition = #RowNumber.RowNumber
FROM tblGroups, #RowNumber
WHERE tblGroups.GroupId = #RowNumber.GroupId

DROP TABLE #RowNumber
GO

Cheers,

Wes