How to get in one query an item and another item having one of its value nearest of the former one?

Imagine I have the following table :

ID || Order
-----------
1  || 1
2  || 2
3  || 5
4  || 20
5  || 100
6  || 4000

(no specific rule applies to the order value).

I want to "move up" ou "move down" items by swapping order values.

Ex: a call to MoveItemUp(4) will results in this new table values :

ID || Order
-----------
1  || 1
2  || 2
3  || 20   <-- swapped order value
4  || 5    <-- swapped order value
5  || 100
6  || 4000

I want to do this in a single query, but I was not yet successful.

The following query works if items order are sequential, with no "hole" (steps of 1 :)

UPDAT开发者_C百科E dbo.ITEMS
set ORDER = case when c.ORDER = c2.ORDER  then c.ORDER  +1 else c.ORDER -1 end

from dbo.ITEMS c
    inner join dbo.ITEMS c2 on c.ORDER   = c2.ORDER   or c.ORDER  = c2.ORDER  + 1
where c2.ID=4

However, I was not able to change this query to support hole. I'm trying to do :

UPDATE dbo.ITEMS
    set case when c.ORDER  = c2.ORDER then min(c2.ORDER ) else c2.ORDER   end
FROM dbo.ITEMS c
    inner join ITEMS c2 on c2.ORDER >= c.ORDER 
    where c2.ID=4
group by c.CAT_ID, c.ORDER 
having c.ORDER = min(c2.ORDER ) or c.ORDER  = c2.ORDER 

However, this does not works as expected (the query updates all items having a greater order instead of the two orders to swap).

PS: I'm working with C# 2.0 on Sybase ASE 4.5, but I assume this question is not specific to this platform. If you have a MSSQL, MySql or Oracle equivalent, I'll put effort to convert it ;)

NOTE All below solutions assume that ItemOrder is unique

EDIT Adding a solution that is more like what OP was trying, and may be more portable to Sybase, this time on Microsoft SQL Server 2008. (See below for solutions using Oracle's analytic functions, that may be more efficient if available.)

First the select to get our row selection criteria correct:

declare @MoveUpId int
set @MoveUpId = 4

select current_row.Id
    , current_row.ItemOrder
    , prior_row.id as PriorRowId
    , prior_row.ItemOrder as PriorItemOrder
    , next_row.id as NextRowId
    , next_row.ItemOrder as NextItemOrder
from #Items current_row
left outer join #Items prior_row
    on prior_row.ItemOrder = (select max(ItemOrder) 
        from #Items 
        where ItemOrder < current_row.ItemOrder)
left outer join #Items next_row 
    on next_row.ItemOrder = (select min(ItemOrder) 
        from #Items 
        where ItemOrder > current_row.ItemOrder)
where @MoveUpId in (current_row.id, next_row.id)

Then the update based on the above:

update current_row
set ItemOrder = case 
    when current_row.Id = @MoveUpId then prior_row.ItemOrder
    else next_row.ItemOrder end
from #Items current_row
left outer join #Items prior_row
    on prior_row.ItemOrder = (select max(ItemOrder) 
        from #Items 
        where ItemOrder < current_row.ItemOrder)
left outer join #Items next_row 
    on next_row.ItemOrder = (select min(ItemOrder)
        from #Items 
        where ItemOrder > current_row.ItemOrder)
where @MoveUpId in (current_row.id, next_row.id)

Id  ItemOrder
1   1
2   2
3   20
4   5
5   100
6   4000
10  -1
20  -2

Set @MoveUpId to 20 and rerun above query results in:

Id  ItemOrder
1   1
2   2
3   20
4   5
5   100
6   4000
10  -2
20  -1

but I assume this question is not specific to this platform. The question may not be specific, but the answer probably is. For example, using Oracle, first, a table and some test data:

create table Items (Id number(38) not null
    , ItemOrder number);

insert into items values (1, 1);
insert into items values (2, 2);
insert into items values (3, 5);
insert into items values (4, 20);
insert into items values (5, 100);
insert into items values (6, 4000);
insert into items values (10, -1);
insert into items values (20, -2);
commit;

Next create a query that returns just the rows we want to update with their new values for Order. (Which I named ItemOrder, Order being a reserved word and all.) In Oracle this is simpliest using the analytic functions lag and lead:

select *
from (select Id
        , ItemOrder
        , lead(Id) over (order by Id) as LeadId
        , lead(ItemOrder) over (order by Id) as LeadItemOrder
        , lag(ItemOrder) over (order by Id) as LagItemOrder
    from Items)
where 4 in (Id, LeadId)
order by Id;

        ID  ITEMORDER     LEADID LEADITEMORDER LAGITEMORDER
---------- ---------- ---------- ------------- ------------
         3          5          4            20            2
         4         20          5           100            5

Convert that into an update statement. However the above query will not create an updateable view (in Oracle), so use merge instead:

merge into Items TRGT
using (select Id
        , ItemOrder
        , lead(Id) over (order by Id) as LeadId
        , lead(ItemOrder) over (order by Id) as LeadItemOrder
        , lag(ItemOrder) over (order by Id) as LagItemOrder
    from Items) SRC
on (SRC.Id = TRGT.Id)
when matched then update 
set ItemOrder = case TRGT.Id 
    when 4 then SRC.LagItemOrder 
    else SRC.LeadItemOrder end
where 4 in (SRC.Id, SRC.LeadId);

select * from Items order by Id;

        ID  ITEMORDER
---------- ----------
         1          1
         2          2
         3         20
         4          5
         5        100
         6       4000
        10         -1
        20         -2

Unfortunately, I do not believe lag and lead are widely implemented. Microsoft SQL Server, as far as I know, has yet to implement them. No experience with ASE, it they have them great.

Row_number() is more widely implemented. Row_number() can be used to get something that is gap free. (Row_number() is refered to as an analytic function on Oracle and a windowed function on SQL Server.) First the query:

with t as (select Id
        , ItemOrder
        , row_number() over (order by Id) as RN
    from Items)
select current_row.id
    , current_row.ItemOrder
    , next_row.Id as NextId
    , next_row.ItemOrder NextItemOrder
    , prior_row.ItemOrder PriorItemOrder
from t current_row
left outer join t next_row on next_row.RN = current_row.RN + 1
left outer join t prior_row on prior_row.RN = current_row.RN - 1
where 4 in (current_row.id, next_row.id);

        ID  ITEMORDER     NEXTID NEXTITEMORDER PRIORITEMORDER
---------- ---------- ---------- ------------- --------------
         3          5          4            20              2
         4         20          5           100              5

Doing the update, again with merge instead of update. (Oracle does allow the update ... from ... join ... syntax, one may be able to get away with update instead of merge on other platforms.)

merge into Items TRGT
using (with t as (select Id
            , ItemOrder
            , row_number() over (order by Id) as RN
        from Items)
    select current_row.id
        , current_row.ItemOrder
        , next_row.Id as NextId
        , next_row.ItemOrder as NextItemOrder
        , prior_row.ItemOrder as PriorItemOrder
    from t current_row
    left outer join t next_row on next_row.RN = current_row.RN + 1
    left outer join t prior_row on prior_row.RN = current_row.RN - 1
    where 4 in (current_row.id, next_row.id)) SRC
on (TRGT.Id = SRC.Id)
when matched then update 
set ItemOrder = case 
    when TRGT.Id = 4 then SRC.PriorItemOrder 
    else SRC.NextItemOrder end;

select *
from Items
order by Id;

        ID  ITEMORDER
---------- ----------
         1          1
         2          2
         3         20
         4          5
         5        100
         6       4000
        10         -1
        20         -2

NOTE Note the solutions above will write null over OrderItems if matching against the Id for the first row.