|
|
Posted by masri999 on 10/27/06 23:13
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
eric.nave@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.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.
Navigation:
[Reply to this message]
|