Row sorting in a table with SQL SERVER

Asked

Viewed 788 times

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) from the position to change and the new position (@novaOrdem) for that ID.

  • If you receive, for example, the 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 stay (Marta Joao Ana)

I managed to solve my problem using a cursor (next code), however I would like to know if there was to solve 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 anyone tell me if it’s possible? Cumps

sql sql-server
  • 1

    I did not understand the ordering rule but often a CASE in ORDER BY , something like ORDER BY (CASEN WHEN P_ORDEM='N' THEN NAME ELSE END CODE) or sort according to a past parameter.

  • What you really need is to leave the data already sorted in the table or when performing a query get the same sorted?

1 answer

1

Maybe I’m being too simplistic, but you can do this ordering with 2 updates conditional:

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;

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.