Change the sequence using SQL Query

Question

Want to sequence with SQL query. Is there any shorter way to update it, or should I update the whole table.

My table as follows:

DECLARE @tab TABLE (ID INT IDENTITY, Name VARCHAR(10), Seq INT)

INSERT INTO @tab VALUES('A',1),('B',1),('C',1),('D',3),('E',4),('F',5),('G',6),('H',7),('I',8)

SELECT * FROM @tab ORDER BY Seq

I want to change the sequence show column with ID 7, 8, 9 at the top. My desired output should be

DECLARE @tab TABLE (ID INT IDENTITY, Name VARCHAR(10), Seq INT)

INSERT INTO @tab VALUES('A',4),('B',5),('C',6),('D',7),('E',8),('F',9),('G',1),('H',2),('I',3)
SELECT * FROM @tab ORDER BY Seq

So what is the problem you're having here? You haven't asked a question just stated what you want, but not explained why you haven't done whatvyou want (if you know what you need to be, please do it). Are you looking for someone to write you an ` UPDATE` statement? What have you tried so far? SO isn't a free coding service, so please help us help you and explain what the problem you're having is, show your attempts and ask a question we can answer. Thanks. — Thom A
– Thom A ♦, Commented May 30, 2019 at 6:48
I can use update statement, but that will take a lot of time as there are many records, just want to know if there is any shortcut to that I can update them quickly — Rahul Aggarwal
– Rahul Aggarwal, Commented May 30, 2019 at 6:53
Do you actually need to change the data? Or just change the order e.g. order by Seq desc? — Dale K
– Dale K, Commented May 30, 2019 at 6:54

EzLo · Accepted Answer · 2019-05-30 06:59:58Z

1

ROW_NUMBER is your friend here, just have to compute 2 different ones depending on the name breaking point.

;WITH RowNumbers AS
(
    SELECT 
        T.ID,
        RegularRowNumber = 3 + ROW_NUMBER() OVER (ORDER BY T.Name),
        AfterGRowNumber = -6 + ROW_NUMBER() OVER (ORDER BY T.Name),
        T.Seq,
        T.Name
    FROM 
        @tab AS T
)
UPDATE R SET
    Seq = CASE WHEN R.Name >= 'G' THEN AfterGRowNumber ELSE RegularRowNumber END
FROM
    RowNumbers AS R

Result:

ID  Name    Seq
1   A       4
2   B       5
3   C       6
4   D       7
5   E       8
6   F       9
7   G       1
8   H       2
9   I       3

Might want to consider using ID to order instead of Name, if appropriate.

answered May 30, 2019 at 6:59

EzLo

14.2k10 gold badges38 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Change the sequence using SQL Query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related