Sorting rows in a table with SQL SERVER

Question

Sorting rows in a table with SQL SERVER

Navigation

#1 by (1 votes)

2

I have a table (Customer) example:

ID  Nome   Order
1   Joao   1
2   Ana    2
3   Marta  3
4   Joana  4

The goal is to sort the contents as follows: I get the ID (@ID) of the position to be changed and the new position (@novaOrdem) for that ID.

If you receive, for example, ID 1 for the 4th position, the data should be (Ana Marta Joana Joao)
If you receive, for example, ID 3 for position 1, the data should be (Marta Joao Ana joana)

I was able to solve my problem using a cursor (next code), but I wondered if I would solve it without using the cursor.

 DECLARE  @idC BIGINT,
  @ordem INT,
  @OldOrdem INT,
  @cont INT = 1;--cont tem que ser 1 para adicionar +1

  SET @OldOrdem = (SELECT Order FROM Customer WHERE ID = @ID)
 IF(@OldOrdem < @novaOrdem)
    BEGIN
        DECLARE Ordem_Cursor CURSOR FOR
            SELECT ID, Order FROM Customer WHERE Order <= @novaOrdem AND ID <> @ID AND Order<>0 ORDER BY Order ASC
            UPDATE Customer SET Order = @novaOrdem  WHERE ID = @ID; /*parametro*/
            OPEN Ordem_Cursor
            FETCH NEXT FROM Ordem_Cursor INTO @idC , @ordem 
            WHILE @@FETCH_STATUS = 0
              BEGIN
            UPDATE  Customer SET Order = @cont WHERE ID = @idC AND Order<>0
            SET @cont = @cont + 1 
             FETCH NEXT FROM Ordem_Cursor INTO @idC , @ordem
            END;
        CLOSE Ordem_Cursor
        DEALLOCATE Ordem_Cursor
     END
ELSE
 BEGIN
    DECLARE Ordem_Cursor CURSOR FOR
        SELECT ID, Order FROM Customer WHERE Order >= @novaOrdem  AND ID <> @ID  AND Order<>0 ORDER BY Order ASC-- selecionar os campos da tabela da mesma page e por ordem crescente
        UPDATE Customer SET Order = @novaOrdem  WHERE ID = @ID; /*parametro*/
        OPEN Ordem_Cursor
        FETCH NEXT FROM Ordem_Cursor INTO @idC , @ordem 
        WHILE @@FETCH_STATUS = 0
          BEGIN
        UPDATE  Customer SET Order = @novaOrdem + @cont WHERE ID = @idC AND Order<>0
        SET @cont = @cont + 1 
         FETCH NEXT FROM Ordem_Cursor INTO @idC , @ordem
        END;
    CLOSE Ordem_Cursor
    DEALLOCATE Ordem_Cursor
END

Can someone tell me if it's possible? Cumps

sql sql-server

asked by anonymous 04.07.2014 / 10:35

1 answer

FullCalendar fetch data from the php database JSF dataTable how to populate the columns with ListListInteger?

score 1 · Answer 1

Maybe I'm being too simplistic, but you can do this with 2 updates conditionals:

DECLARE @idC BIGINT,
    @novaOrdem INT,
    @oldOrdem INT;
SET @idC=1;
SET @novaOrdem=4;
--Obtém a Ordem do ID que estamos alterando
SET @oldOrdem=(SELECT Order FROM Customer WHERE ID=@idC);
--Se for mover para baixo (@oldOrdem < @novaOrdem), quem estiver no intervalo sobe 1
UPDATE Customer SET Order=Order-1 WHERE @oldOrdem < @novaOrdem AND Order BETWEEN @oldOrdem+1 AND @novaOrdem;
--Se for mover para cima (@oldOrdem > @novaOrdem), quem estiver no intervalo desce 1
UPDATE Customer SET Order=Order+1 WHERE @oldOrdem > @novaOrdem AND Order BETWEEN @novaOrdem AND @oldOrdem-1;
--Coloca o ID no destino
UPDATE Customer SET Order=@novaOrdem WHERE ID=@idC;