Sequential Number in an Update

[eric.nave]

this is a slight change to a fequently asked question around here. I
have a table which contains a "sortorder" column where a user can
specify some arbitrary order for records to be displayed in. Users
have sometimes ordered records the way we used to number lines in a
BASIC program (10,20,30, etc.). I'd like to do an update query and fix
this so that every record is in sequential order. I found an example
in this newsgroup of how to do this.

However, I have a slight problem! Sometimes, the users duplicated the
sortorders. So for example, I might have two records were the
sortorder is 20. The query I found in this newsgroup does not work in
that case. Here is some code so that you can see what I mean.

create table sorttest (
label char(5),
sortorder int
)
go
insert sorttest values ('joe',20)
insert sorttest values ('dan',10)
insert sorttest values ('jim',44)
insert sorttest values ('tom',20)
insert sorttest values ('jan',50)

-- data dump
select label, sortorder from sorttest order by sortorder

-- I'd like to fix all of the sortorder fields so that they are
sequential
update sorttest
set sortorder = (
select count(*)
from sorttest subquery
where sorttest.sortor der <= subquery.sortor der
)

-- note that tom and joe BOTH HAVE SORTORDER = 4
select label, sortorder from sorttest order by sortorder

drop table sorttest
Thanks in advance for any help.

[markc600]

You need additional criteria to sort uniquely.
Try this

update sorttest
set sortorder = (
select count(*)
from sorttest subquery
where sorttest.sortor der < subquery.sortor der
or (sorttest.sorto rder = subquery.sortor der
and sorttest.label <= subquery.label)
)

[masri999]

If it is one tine update you can do this . Note values of sortcolumns
in all the rows will be changed

DECLARE @sortcol INT
SET @sortcol = 0 -- You can set to any other value if you like
UPDATE sorttest
SET @sortcol = sorcol = @sortcol + 1

M A Srinivas
er*******@gmail .com wrote:

this is a slight change to a fequently asked question around here. I
have a table which contains a "sortorder" column where a user can
specify some arbitrary order for records to be displayed in. Users
have sometimes ordered records the way we used to number lines in a
BASIC program (10,20,30, etc.). I'd like to do an update query and fix
this so that every record is in sequential order. I found an example
in this newsgroup of how to do this.

However, I have a slight problem! Sometimes, the users duplicated the
sortorders. So for example, I might have two records were the
sortorder is 20. The query I found in this newsgroup does not work in
that case. Here is some code so that you can see what I mean.

create table sorttest (
label char(5),
sortorder int
)
go
insert sorttest values ('joe',20)
insert sorttest values ('dan',10)
insert sorttest values ('jim',44)
insert sorttest values ('tom',20)
insert sorttest values ('jan',50)

-- data dump
select label, sortorder from sorttest order by sortorder

-- I'd like to fix all of the sortorder fields so that they are
sequential
update sorttest
set sortorder = (
select count(*)
from sorttest subquery
where sorttest.sortor der <= subquery.sortor der
)

-- note that tom and joe BOTH HAVE SORTORDER = 4
select label, sortorder from sorttest order by sortorder

drop table sorttest
Thanks in advance for any help.

[--CELKO--]

>have a table which contains a "sortorder" column where a user can specify some arbitrary order for records to be displayed in. <<

The classic error of mixing display and data in the RDBMS?

>Users have sometimes ordered records [sic] the way we used to number lines in a BASIC program (10, 20, 30, etc.). I'd like to do an update query and fix this so that every record [sic] is in sequential order. <<

Rows and records are totally diffreent concepts. You are still
thinking about a file system, not RDBMS. Line numbers are a physical
representation of a paper form or input screen, not a logical data
model element.

>However, I have a slight problem! Sometimes, the users duplicated the sortorders. <<

Gee, do you suppose that might be due to the fact that they are not
relational, have no rules for validation or verifiication? And the
fact that you do not have a UNIQUE constraint on this silly column????

>Thanks in advance for any help. <<

You might want to read book on RDBMS, data modeling and tiered
architectures

[David Portas]

er*******@gmail .com wrote:

this is a slight change to a fequently asked question around here. I
have a table which contains a "sortorder" column where a user can
specify some arbitrary order for records to be displayed in. Users
have sometimes ordered records the way we used to number lines in a
BASIC program (10,20,30, etc.). I'd like to do an update query and fix
this so that every record is in sequential order. I found an example
in this newsgroup of how to do this.

However, I have a slight problem! Sometimes, the users duplicated the
sortorders. So for example, I might have two records were the
sortorder is 20. The query I found in this newsgroup does not work in
that case. Here is some code so that you can see what I mean.

create table sorttest (
label char(5),
sortorder int
)
go
insert sorttest values ('joe',20)
insert sorttest values ('dan',10)
insert sorttest values ('jim',44)
insert sorttest values ('tom',20)
insert sorttest values ('jan',50)

-- data dump
select label, sortorder from sorttest order by sortorder

-- I'd like to fix all of the sortorder fields so that they are
sequential
update sorttest
set sortorder = (
select count(*)
from sorttest subquery
where sorttest.sortor der <= subquery.sortor der
)

-- note that tom and joe BOTH HAVE SORTORDER = 4
select label, sortorder from sorttest order by sortorder

drop table sorttest
Thanks in advance for any help.

WITH t AS
(SELECT label, sortorder,
ROW_NUMBER() OVER (ORDER BY sortorder,label ) AS new_order
FROM sorttest)
UPDATE t SET sortorder = new_order;

--
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/m...S,SQL.90).aspx
--

[eric.nave]

--CELKO-- wrote:

>
The classic error of mixing display and data in the RDBMS?

This RDBMS is driving a content management system. The table in
question contains a list of URL links - basically a navigation element
for the website. So obviously, the CMS user has a requirement to put
the links in some arbitrary order that makes sense to them.

Imagine that this was the data that I wanted to have on this navigation
element:

http://google.com
http://apple.com

Pray tell how YOU would store those values so that the resulting HTML
page shows them in the order that the user wants?

fact that you do not have a UNIQUE constraint on this silly column????

If you stop and think about this (but that seems to be a handicap for
you) you will realize that there can't be a UNIQUE constraint on the
sortorder column. If you honestly can't figure out why, ask nicely and
maybe I'll explain it to you.

But I really would love to hear how you would keep a list of links in
an arbitrary order

[--CELKO--]

>there can't be a UNIQUE constraint on the sortorder column.<<

What is the nature of a sort on a set? It is a sequence; every element
has a unique position in that sequence. This is by definition.

>But I really would love to hear how you would keep a list of links in an arbitrary order <<

Now we have some more specs to use.

CREATE TABLE UserLinks
(user_id INTEGER NOT NULL
REFERENCES Users(user_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
preference_nbr INTEGER NOT NULL
CHECK (preference_nbr 0),
content_url VARCHAR (255) NOT NULL
CHECK (content_url LIKE 'http://%.com'), -- or whatever grep() you
need
PRIMARY KEY (user_id, preference_nbr) ); -- here is your unique
constraint

You will need a proc to move them around once they are in the table.

CREATE PROCEDURE SwapUserLinks
(@my_user_id INTEGER, @old_preference _nbr INTEGER, @new_preference _nbr
INTEGER)
AS
UPDATE UserLinks
SET preference_nbr
= CASE preference_nbr
WHEN @old_preference _nbr
THEN @new_preference _nbr
ELSE preference_nbr + SIGN(@old_prefe rence_nbr - @new_pos)
END
WHERE user_id = @my_user_id
AND (preference_nbr BETWEEN @old_preference _nbr AND
@new_preference _nbr
OR preference_nbr BETWEEN @new_preference _nbr AND
@old_preference _nbr);

Play with it a little bit. It takes a starting position in the list
and moves it up or down to the new position and slides the elements in
between either up or down.

When you want to drop a few rows, remember to close the gaps with this
code. It will start the numbering at 1 within each user.

CREATE PROCEDURE CloseUserLinksG aps(@my_user_id INTEGER)
AS
UPDATE UserLinks
SET preference_nbr
= (SELECT COUNT (U1.preference_ nbr)
FROM UserLinks AS U1
WHERE U1.preference_n br <= UserLinks.prefe rence_nbr
AND user_id = @my_user_id
AND @my_user_id = UserLinks.user_ id);

[--CELKO--]

>there can't be a UNIQUE constraint on the sortorder column.<<

What is the nature of a sort on a set? It is a sequence; every element
has a unique position in that sequence. This is by definition.

>But I really would love to hear how you would keep a list of links in an arbitrary order <<

Now we have some more specs to use.

CREATE TABLE UserLinks
(user_id INTEGER NOT NULL
REFERENCES Users(user_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
preference_nbr INTEGER NOT NULL
CHECK (preference_nbr 0),
content_url VARCHAR (255) NOT NULL
CHECK (content_url LIKE 'http://%.com'), -- or whatever grep() you
need
PRIMARY KEY (user_id, preference_nbr) ); -- here is your unique
constraint

You will need a proc to move them around once they are in the table.

CREATE PROCEDURE SwapUserLinks
(@my_user_id INTEGER, @old_preference _nbr INTEGER, @new_preference _nbr
INTEGER)
AS
UPDATE UserLinks
SET preference_nbr
= CASE preference_nbr
WHEN @old_preference _nbr
THEN @new_preference _nbr
ELSE preference_nbr + SIGN(@old_prefe rence_nbr - @new_pos)
END
WHERE user_id = @my_user_id
AND (preference_nbr BETWEEN @old_preference _nbr AND
@new_preference _nbr
OR preference_nbr BETWEEN @new_preference _nbr AND
@old_preference _nbr);

Play with it a little bit. It takes a starting position in the list
and moves it up or down to the new position and slides the elements in
between either up or down.

When you want to drop a few rows, remember to close the gaps with this
code. It will start the numbering at 1 within each user.

CREATE PROCEDURE CloseUserLinksG aps(@my_user_id INTEGER)
AS
UPDATE UserLinks
SET preference_nbr
= (SELECT COUNT (U1.preference_ nbr)
FROM UserLinks AS U1
WHERE U1.preference_n br <= UserLinks.prefe rence_nbr
AND user_id = @my_user_id
AND @my_user_id = UserLinks.user_ id);

[Tony Rogerson]

CHECK (content_url LIKE 'http://%.com'), -- or whatever grep() you

Interesting, I would really like to know how you would validate this as a
URL (or email address) in a constraint.

You need to look for a lot more that http:// and .com; there is http / https
for a start, ftp: there is .com, .co.uk, .net and about 2 dozen other
domains and there is sub-domains to handle.....

The only way you are going to write a safe constraint like this is to use
regular expressions and for that you'll need CLR.

So, come on celko - just how would you write this contraint to truely
validate the url is valid and also protect yourself from a DBA making an
accidental update (or melicious....)? ?????

--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jc*******@eart hlink.netwrote in message
news:11******** **************@ e64g2000cwd.goo glegroups.com.. .

>>there can't be a UNIQUE constraint on the sortorder column.<<

What is the nature of a sort on a set? It is a sequence; every element
has a unique position in that sequence. This is by definition.

>>But I really would love to hear how you would keep a list of links in an
arbitrary order <<

Now we have some more specs to use.

CREATE TABLE UserLinks
(user_id INTEGER NOT NULL
REFERENCES Users(user_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
preference_nbr INTEGER NOT NULL
CHECK (preference_nbr 0),
content_url VARCHAR (255) NOT NULL
CHECK (content_url LIKE 'http://%.com'), -- or whatever grep() you
need
PRIMARY KEY (user_id, preference_nbr) ); -- here is your unique
constraint

You will need a proc to move them around once they are in the table.

CREATE PROCEDURE SwapUserLinks
(@my_user_id INTEGER, @old_preference _nbr INTEGER, @new_preference _nbr
INTEGER)
AS
UPDATE UserLinks
SET preference_nbr
= CASE preference_nbr
WHEN @old_preference _nbr
THEN @new_preference _nbr
ELSE preference_nbr + SIGN(@old_prefe rence_nbr - @new_pos)
END
WHERE user_id = @my_user_id
AND (preference_nbr BETWEEN @old_preference _nbr AND
@new_preference _nbr
OR preference_nbr BETWEEN @new_preference _nbr AND
@old_preference _nbr);

Play with it a little bit. It takes a starting position in the list
and moves it up or down to the new position and slides the elements in
between either up or down.

When you want to drop a few rows, remember to close the gaps with this
code. It will start the numbering at 1 within each user.

CREATE PROCEDURE CloseUserLinksG aps(@my_user_id INTEGER)
AS
UPDATE UserLinks
SET preference_nbr
= (SELECT COUNT (U1.preference_ nbr)
FROM UserLinks AS U1
WHERE U1.preference_n br <= UserLinks.prefe rence_nbr
AND user_id = @my_user_id
AND @my_user_id = UserLinks.user_ id);

[--CELKO--]

>I would really like to know how you would validate this as a URL (or email address) in a constraint. <<

I would Google it and find this website, which is full of handy regular
expressions for my constraints. This page gives ten answers.

http://regexlib.com/Search.aspx?k=URL

I guess cowboys post first and Google later :)

> The only way you are going to write a safe constraint like this is to use regular expressions and for that you'll need CLR. <<

No CLR, Tony. You need the SIMILAR TO operator in SQL-92 or a vendor
grep() functions such as Oracle and DB2 have. In SQL Server, you will
still have an ugly translation today. The x|y operator becomes <exp>
LIKE x OR <exp:LIKE y and so forth. There was an old public domain
parser for grep() to SQL Server dialect, but I cannot find it on my
disk. It probably should be re-done with CASE expressions.

>How would you write this constraint to truly validate the URL is valid and also protect yourself from a DBA making an accidental update (or malicious....)? <<

Nothing can protect you from a malicious DBA; with full DBA powers he
can destroy the world.

"On two occasions I have been asked, 'Pray, Mr. Babbage, if you put
into the machine wrong figures, will the right answers come out?' I am
not able rightly to apprehend the kind of confusion of ideas that could
provoke such a question." -- Charles Babbage

But you are confusing two different concepts; validation and
verification. A constraint can only validate a data element (This is a
syntactically correct URL) and verification (This URL actually belongs
to someone). Verification has to be external to the schema by
definition.

The way people verify a URL is to send an email to it. It is like
having a GPS tool or sextant to verify a (long, lat) location.