|
|
Posted by David Portas on 10/28/06 06:53
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.
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/ms130214(en-US,SQL.90).aspx
--
Navigation:
[Reply to this message]
|