Order in the middle of a table

Hi,
I publish a table on a website. At the end of each row I want to show an up-
and downbutton. The buttons haves an id as parameter. Inside my table I have
a vieworder column, this is the way I sort the table.
What I want is that when you press on the up- or downbutton that record
moves up or down. This means an update of that record and the record above
or below. I want to know what is the best way how to realize this.
Ideas? Are there (build-in) funtions for this?
Thank!You could easily write a stored proc to do this. One of the many
wonderful things about the UPDATE statement in the SQL language is that
it is an all-at-once operation. You can increment the vieworder for a
row and either increment or decrement its neighbour as appropriate all
at the same time. Try this batch:
use tempdb
go
-- A little setting up...
create table #tmp
(
vieworder int not null,
blah char(1) primary key clustered
)
insert into #tmp (vieworder, blah) values (1, 'A')
insert into #tmp (vieworder, blah) values (2, 'B')
insert into #tmp (vieworder, blah) values (3, 'C')
insert into #tmp (vieworder, blah) values (4, 'D')
insert into #tmp (vieworder, blah) values (5, 'E')
insert into #tmp (vieworder, blah) values (6, 'F')
insert into #tmp (vieworder, blah) values (7, 'G')
insert into #tmp (vieworder, blah) values (8, 'H')
insert into #tmp (vieworder, blah) values (9, 'I')
insert into #tmp (vieworder, blah) values (10, 'J')
-- --
-- the row to shuffle
declare @.id int
set @.id = 10
-- The original result set
select * from #tmp order by vieworder
-- Shuffle up
update #tmp
set vieworder =
case
when (vieworder = @.id) then (vieworder + 1)
when (vieworder = @.id + 1) then (vieworder - 1)
end
where (vieworder = @.id or vieworder = @.id + 1) -- Only
shuffle the 2 rows in question
and exists (select * from #tmp where vieworder = @.id + 1) -- Just
to make sure you don't shuffle a row "off the end"
-- Let's see it after the shuffle up
select * from #tmp order by vieworder
-- Shuffle down
update #tmp
set vieworder =
case
when (vieworder = @.id) then (vieworder - 1)
when (vieworder = @.id - 1) then (vieworder + 1)
end
where (vieworder = @.id or vieworder = @.id - 1) -- Only
shuffle the 2 rows in question
and exists (select * from #tmp where vieworder = @.id - 1) -- Just
to make sure you don't shuffle a row "off the end"
-- Let's see it after the shuffle down
select * from #tmp order by vieworder
-- Tidy up
drop table #tmp
Embed that kind of logic inside a stored procedure and then you just
call the stored proc with the appropriate parameters (a direction to
shuffle and a baseline row to shuffle along with its neighbour) from
your webpage using ADO (with a postback to reload the same webpage I guess).
Hope this helps.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Arjen wrote:

>Hi,
>I publish a table on a website. At the end of each row I want to show an up
-
>and downbutton. The buttons haves an id as parameter. Inside my table I hav
e
>a vieworder column, this is the way I sort the table.
>What I want is that when you press on the up- or downbutton that record
>moves up or down. This means an update of that record and the record above
>or below. I want to know what is the best way how to realize this.
>Ideas? Are there (build-in) funtions for this?
>Thank!
>
>|||Okay, thanks!
I will try this.
Arjen
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> schreef in bericht news:e
yGZJViqFHA.564@.TK2MSFTNGP10.phx.gbl...
You could easily write a stored proc to do this. One of the many wonderful
things about the UPDATE statement in the SQL language is that it is an all-a
t-once operation. You can increment the vieworder for a row and either incr
ement or decrement its neighbour as appropriate all at the same time. Try t
his batch:
use tempdb
go
-- A little setting up...
create table #tmp
(
vieworder int not null,
blah char(1) primary key clustered
)
insert into #tmp (vieworder, blah) values (1, 'A')
insert into #tmp (vieworder, blah) values (2, 'B')
insert into #tmp (vieworder, blah) values (3, 'C')
insert into #tmp (vieworder, blah) values (4, 'D')
insert into #tmp (vieworder, blah) values (5, 'E')
insert into #tmp (vieworder, blah) values (6, 'F')
insert into #tmp (vieworder, blah) values (7, 'G')
insert into #tmp (vieworder, blah) values (8, 'H')
insert into #tmp (vieworder, blah) values (9, 'I')
insert into #tmp (vieworder, blah) values (10, 'J')
-- --
-- the row to shuffle
declare @.id int
set @.id = 10
-- The original result set
select * from #tmp order by vieworder
-- Shuffle up
update #tmp
set vieworder =
case
when (vieworder = @.id) then (vieworder + 1)
when (vieworder = @.id + 1) then (vieworder - 1)
end
where (vieworder = @.id or vieworder = @.id + 1) -- Only shuffle t
he 2 rows in question
and exists (select * from #tmp where vieworder = @.id + 1) -- Just to make s
ure you don't shuffle a row "off the end"
-- Let's see it after the shuffle up
select * from #tmp order by vieworder
-- Shuffle down
update #tmp
set vieworder =
case
when (vieworder = @.id) then (vieworder - 1)
when (vieworder = @.id - 1) then (vieworder + 1)
end
where (vieworder = @.id or vieworder = @.id - 1) -- Only shuffle t
he 2 rows in question
and exists (select * from #tmp where vieworder = @.id - 1) -- Just to make s
ure you don't shuffle a row "off the end"
-- Let's see it after the shuffle down
select * from #tmp order by vieworder
-- Tidy up
drop table #tmp
Embed that kind of logic inside a stored procedure and then you just call th
e stored proc with the appropriate parameters (a direction to shuffle and a
baseline row to shuffle along with its neighbour) from your webpage using AD
O (with a postback to reload the same webpage I guess).
Hope this helps.
mike hodgson
blog: http://sqlnerd.blogspot.com
Arjen wrote:
Hi,
I publish a table on a website. At the end of each row I want to show an up-
and downbutton. The buttons haves an id as parameter. Inside my table I have
a vieworder column, this is the way I sort the table.
What I want is that when you press on the up- or downbutton that record
moves up or down. This means an update of that record and the record above
or below. I want to know what is the best way how to realize this.
Ideas? Are there (build-in) funtions for this?
Thank!