Posted by eric.nave on 10/27/06 16:36

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.sortorder <= subquery.sortorder
)

-- 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.

[Back to original message]