Changing Display Order of Data

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Changing Display Order of Data

Author

Topic

Wannabe67
Starting Member

14 Posts

Posted - 2009-12-10 : 11:12:19

I have a web page where a user drags a person to a new location. When they do this, I need to re-order a column I have, DisplayOrder, to the new order that reflects on the user's screen. It sounds simple, but the logic is not so simple. Does anyone have some t-sql that does this, or can help me on this? So, with the example below, if person3 gets moved to the top, their display order needs to change to 1. Person1 and Person2 need to be incremented and person4 needs to stay the same. Any help would be greatly appreciated.


User       Title       DisplayOrder
person1    BMOC        1
person2    Geek        2
person3    Wannabe     3
person4    Nobody      4

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-12-10 : 11:18:15

can you show us the table in question from where you get/update your data?
also how big can your list be?

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-10 : 11:26:56

[code]declare @t table ([User] varchar(9), Title varchar(9), DisplayOrder int)
insert @t
select 'person1', 'BMOC', 1
union all select 'person2', 'Geek', 2
union all select 'person3', 'Wannabe', 3
union all select 'person4', 'Nobody', 4

declare @MoveDisplayOrderFrom int;
declare @MoveDisplayOrderTo int;
set @MoveDisplayOrderFrom = 3;
set @MoveDisplayOrderTo = 1;

update @t set DisplayOrder =
case
when DisplayOrder = @MoveDisplayOrderFrom then @MoveDisplayOrderTo
when DisplayOrder between @MoveDisplayOrderFrom and @MoveDisplayOrderTo then DisplayOrder-1
when DisplayOrder between @MoveDisplayOrderTo and @MoveDisplayOrderFrom then DisplayOrder+1
end
from @t
where DisplayOrder between @MoveDisplayOrderFrom and @MoveDisplayOrderTo
or DisplayOrder between @MoveDisplayOrderTo and @MoveDisplayOrderFrom

select * from @t order by DisplayOrder[/code]

Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.

Wannabe67
Starting Member

14 Posts

Posted - 2009-12-10 : 11:38:31

quote:

Originally posted by RyanRandall

declare @t table ([User] varchar(9), Title varchar(9), DisplayOrder int)
insert @t
          select 'person1', 'BMOC', 1
union all select 'person2', 'Geek', 2
union all select 'person3', 'Wannabe', 3
union all select 'person4', 'Nobody', 4

declare @MoveDisplayOrderFrom int;
declare @MoveDisplayOrderTo int; 
set @MoveDisplayOrderFrom = 3;
set @MoveDisplayOrderTo = 1;

update @t set DisplayOrder =
    case
        when DisplayOrder = @MoveDisplayOrderFrom then @MoveDisplayOrderTo
        when DisplayOrder between @MoveDisplayOrderFrom and @MoveDisplayOrderTo then DisplayOrder-1
        when DisplayOrder between @MoveDisplayOrderTo and @MoveDisplayOrderFrom then DisplayOrder+1
    end
from @t
where DisplayOrder between @MoveDisplayOrderFrom and @MoveDisplayOrderTo
   or DisplayOrder between @MoveDisplayOrderTo and @MoveDisplayOrderFrom

select * from @t order by DisplayOrder

Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.

This works great! My attempts were making it much harder than it had to be. Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources