get the next in line... - SQL Server Forums

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)

get the next in line...

Author

Topic

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2008-04-22 : 11:53:48

Hi, i've got a table which holds a field called order.
Let's say it looks like this

ItemId Name Order
223 test 1 1
542 test 5 34
23 test 2 4
676 test 123 2

I'm building a website, which gets the records by the right order.
So it displays this on the website:

test 1
test 123
test 2
test 5

Now, I'm building a up/down function.
So when I click down on 'test 123' it should swap the order with 'test 2'

So the database will look like this:
ItemId Name Order
223 test 1 1
542 test 5 34
23 test 2 2
676 test 123 4

And the website will look like this:

test 1
test 2
test 123
test 5

And it should also work the other way around with the up button.

So my guess is, I have the order of the item I clicked on (2) and I should get the order of the previous or next record (depending on which button I clicked).
So how would I do that?

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-22 : 12:02:28

Do you want the changeto be permanent? i.e. change the order value in the database.

I would probably have a stored procedured that tables 2 IDs and swaps the order value of them and leave it to the client to pass the IDs.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2008-04-22 : 12:10:09

No, you see that's just the problem, I only have the id of the item to move up or down.
I need something which can find the next (or previous) item.

And perhaps something to update the two.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-04-22 : 12:16:38

First stab...

declare @t table (ItemId int, Name varchar(10), [Order] int)
insert @t
          select 223, 'test 1', 1
union all select 542, 'test 5', 34
union all select 23, 'test 2', 4
union all select 676, 'test 123', 2

select * from @t order by [Order]

--Move ItemId 676 down
update b set [Order] = case when [Order] = NewOrder or NewOrder is null then OldOrder else NewOrder end from (
    select [Order] as OldOrder,
        (select top 1 [Order] from @t where [Order] > a.[Order] order by [Order]) as NewOrder
    from @t a where ItemId = 676) a inner join @t b on a.OldOrder = b.[Order] or a.NewOrder = b.[Order]

select * from @t order by [Order]

--Move ItemId 676 up
update b set [Order] = case when [Order] = NewOrder or NewOrder is null then OldOrder else NewOrder end
from (
    select [Order] as OldOrder,
        (select top 1 [Order] from @t where [Order] < a.[Order] order by [Order] desc) as NewOrder
    from @t a where ItemId = 676) a inner join @t b on a.OldOrder = b.[Order] or a.NewOrder = b.[Order]

select * from @t order by [Order]

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2008-04-22 : 12:51:05

Hi, I am trying to make a SP from this. But can't get it to work.
I think I know not enough about SQL...
Can you help with this?

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-04-22 : 12:59:01

Something along the lines of...

create table myTable (ItemId int, Name varchar(10), [Order] int)
insert myTable
          select 223, 'test 1', 1
union all select 542, 'test 5', 34
union all select 23, 'test 2', 4
union all select 676, 'test 123', 2
go

create proc myProc @ItemId int, @IsUp bit as

--Move ItemId @ItemId down
if @IsUp = 0
update b set [Order] = case when [Order] = NewOrder or NewOrder is null then OldOrder else NewOrder end from (
    select [Order] as OldOrder,
        (select top 1 [Order] from myTable where [Order] > a.[Order] order by [Order]) as NewOrder
    from myTable a where ItemId = @ItemId) a inner join myTable b on a.OldOrder = b.[Order] or a.NewOrder = b.[Order]

--Move ItemId @ItemId up
if @IsUp = 1
update b set [Order] = case when [Order] = NewOrder or NewOrder is null then OldOrder else NewOrder end from (
    select [Order] as OldOrder,
        (select top 1 [Order] from myTable where [Order] < a.[Order] order by [Order] desc) as NewOrder
    from myTable a where ItemId = @ItemId) a inner join myTable b on a.OldOrder = b.[Order] or a.NewOrder = b.[Order]
go

select * from dbo.myTable order by [Order]
exec myProc 676, 0
select * from dbo.myTable order by [Order]
exec myProc 676, 0
select * from dbo.myTable order by [Order]
exec myProc 676, 0
select * from dbo.myTable order by [Order]
exec myProc 676, 0
select * from dbo.myTable order by [Order]
exec myProc 676, 1
select * from dbo.myTable order by [Order]
exec myProc 676, 1
select * from dbo.myTable order by [Order]
exec myProc 676, 1
select * from dbo.myTable order by [Order]
exec myProc 676, 1
select * from dbo.myTable order by [Order]
exec myProc 676, 1

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2008-04-22 : 13:07:33

Wow, where can you get that kind of service today....
Thanks a lot.

The secret to creativity is knowing how to hide your sources. (Einstein)

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-04-22 : 13:12:41

I suppose you could wrap the update up into one query, but I wouldn't necessarily recommend it...

update b set [Order] = case when [Order] = NewOrder or NewOrder is null then OldOrder else NewOrder end from (
    select [Order] as OldOrder,
        (select top 1 [Order] from myTable
         where (@IsUp = 0 and [Order] > a.[Order]) or (@IsUp = 1 and [Order] < a.[Order])
         order by -(cast(@IsUp as smallint)*2-1) * [Order]) as NewOrder
    from myTable a where ItemId = @ItemId) a inner join myTable b on a.OldOrder = b.[Order] or a.NewOrder = b.[Order]

Shorter, but probably trickier to maintain - you'd have to test the speed for yourself if that's important.

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-04-22 : 13:13:42

quote:
Originally posted by trouble2

Wow, where can you get that kind of service today....
Thanks a lot.

The secret to creativity is knowing how to hide your sources. (Einstein)

Here on sqlteam...

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources