Reorder records in table

Question

I am looking for a SQL solution to reorder records by updating order column.

Consider following data structure:

------------------------------------
| id | type | value | order | user |
------------------------------------
|  1 |    A |   123 |     1 |    1 |
|  2 |    B |   231 |     2 |    1 |
|  3 |    B |   213 |     3 |    2 |
|  4 |    A |   222 |     4 |    2 |
|  5 |    A |    22 |     5 |    3 |
|  6 |    B |    11 |     6 |    4 |
|  7 |    C |    99 |     7 |    1 |
------------------------------------

Software(non-modifiable) that uses this data, takes order in account and applies value with max order for each user. However I need that type A records are applied first (have max order for particular users entries). Order column is unique.

Current solution is to create temporary table, selecting data in desired order and adding new identify column. Effectively rebuilding whole order column. Like this:

BEGIN TRANSACTION;

-- first we take type C
SELECT id
    ,type
    ,value
    ,[ORDER]
    ,user
INTO #tmp
FROM records
WHERE type = C;

-- we add new identify column
ALTER TABLE #tmp ADD new_order INT identify (1,1);

INSERT INTO #tmp
SELECT id
    ,type
    ,value
    ,[ORDER]
    ,user
FROM records
WHERE type = B;

-- last we take type A, these will have largest `order` values
INSERT INTO #tmp
SELECT id
    ,type
    ,value
    ,[ORDER]
    ,user
FROM records
WHERE type = A;

-- old update method
--update records set records.order = #tmp.new_order from records
--join #tmp on #tmp.id = records.id
COMMIT TRANSACTION;

-- new method thanks @Lennart
MERGE INTO records x
USING #tmp
    ON #tmp.id = records.id
WHEN MATCHED
    THEN
        UPDATE
        SET records.
        ORDER = #tmp.new_order;

DROP TABLE #tmp;

However this solution locks the table for too long, which is not acceptable during business hours.

Is it possible and effective to split this per user?

1. Take all records of user 1

2. Reorder using only order values of given records (not sure how to do this)

3. Update records table

4. Repeat for next user

I understand that the whole process will take much longer, but rest of the table will not be locked for other operations?

I am looking for a SQL solution.

UPDATE

I believe I did not elaborate enough.

Column "order" is unique. I am interested if there is a SQL way to:

1. Get all records for each user. e.g.: select * from records where user = 1;

2. Sort records for each user using values available. In case of user=1 we have only values (1,2,7) to operate with.

3. Update order column in records table

Desired result for give example:

------------------------------------
| id | type | value | order | user |
------------------------------------
|  1 |    A |   123 |     7 |    1 |
|  2 |    B |   231 |     2 |    1 |
|  3 |    B |   213 |     3 |    2 |
|  4 |    A |   222 |     4 |    2 |
|  5 |    A |    22 |     5 |    3 |
|  6 |    B |    11 |     6 |    4 |
|  7 |    C |    99 |     1 |    1 |
------------------------------------ 

UPDATE #2: This is what I theoretically meant. However in practice this will lock the table for much longer periods than current solution and, over time, make all operations with the table slower.

DECLARE @curr_usr INT

DECLARE curr CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR
SELECT DISTINCT user
FROM records

-- foreach user
OPEN curr

FETCH NEXT
FROM curr
INTO @curr_usr

WHILE @@fetch_status = 0
BEGIN
    -- get user entries
    SELECT *
        ,row_number() OVER (
            ORDER BY type
            ) AS new_order
    INTO #tmp
    FROM records
    WHERE user = @curr_usr

    -- sort available order values for given record set
    MERGE INTO #tmp
    USING (
        SELECT oder
            ,row_number() OVER (
                ORDER BY
                [ORDER]
                ) AS order_seq
        FROM records
        WHERE user = @curr_usr
        ) x
        ON x.order_seq = #tmp.new_order
    WHEN MATCHED
        THEN
            UPDATE
            SET #tmp.
            [ORDER] = x.
            [ORDER];

    -- update main table
    MERGE INTO records
    USING #tmp
        ON #tmp.id = records.id
    WHEN MATCHED
        THEN
            UPDATE
            SET records.
            [ORDER] = #tmp.
            [ORDER];

    FETCH NEXT
    FROM curr
    INTO @curr_usr

    TRUNCATE TABLE #tmp
END

CLOSE curr

UPDATE #3 Some clarification about data amount and time

There are roughly 600k records for ~10k users.
solution #1 (stated in question) takes ~ 29 seconds
solution adapted from @lennart answer takes ~ 27 seconds
solution adapted from @daniel answer takes ~ 26 seconds

This is test data, with no users and services working on it, sorter being the only one. I believe that achieving <10 seconds would be enough for clients not to notice the lag.

It appears that I will have to adapt @daniel solution, limited to user where user = ? and call this for each user separately with delay between calls, this way clients should not notice any delays, right?

Answer 1

Table

CREATE TABLE dbo.Records
(
    id integer NOT NULL
        CONSTRAINT [PK dbo.Records id]
        PRIMARY KEY CLUSTERED (id),
    [type] character(1) NOT NULL,
    [value] integer NOT NULL,
    [order] smallint NOT NULL,
    [user] integer NOT NULL,

    -- order is unique
    CONSTRAINT [UQ dbo.Records order]
        UNIQUE NONCLUSTERED ([order] DESC)
);

Indexes

-- type is unique per user, useful to include order
CREATE UNIQUE INDEX [UQ dbo.Records user, type (order)]
ON dbo.Records ([user], [type])
INCLUDE ([order]);

-- useful index on user, order
CREATE NONCLUSTERED INDEX [UQ dbo.Records user, order]
ON dbo.Records ([user] ASC, [order] DESC);

Sample data

INSERT dbo.Records
    (id, [type], [value], [order], [user])
VALUES
    (1, 'A', 123, 1, 1),
    (2, 'B', 231, 2, 1),
    (3, 'B', 213, 3, 2),
    (4, 'A', 222, 4, 2),
    (5, 'A',  22, 5, 3),
    (6, 'B',  11, 6, 4),
    (7, 'C',  99, 7, 1);

Solution

The following is similar to other answers in outline (joining two sets of numbered rows), but it optimizes the join (avoiding nested loops), and avoids changing rows where the order is not changed by the application of the algorithm. Comments inline:

WITH
    Existing AS
    (
        -- Number existing rows in order, per user
        SELECT
            R.[user],
            R.[order],
            rn = ROW_NUMBER() OVER (
                PARTITION BY R.[user] 
                ORDER BY R.[order] DESC)
        FROM dbo.Records AS R 
    ),
    New AS
    (
        -- Number existing rows by type, per user
        SELECT
            R.[user],
            R.[order], 
            rn = ROW_NUMBER() OVER (
                PARTITION BY R.[user] 
                ORDER BY R.[type] ASC)
        FROM dbo.Records AS R 
    ),
    OptNew AS
    (
        -- Help the optimizer see that New.rn is unique per user
        SELECT
            New.[user],
            [order] = MAX(New.[order]), -- Meaningless, but required
            New.rn
        FROM New
        GROUP BY 
            New.[user], 
            New.rn
    )
UPDATE E
SET [order] = N.[order]
FROM Existing AS E
JOIN OptNew AS N
    ON N.[user] = E.[user]
    AND N.rn = E.rn
WHERE
    -- Only update if changed
    N.[order] <> E.[order];

db<>fiddle

Execution plan

Answer 2

You can use a merge statement (untested, but you should get the idea):

merge into records x
using (
    select id, type, value, user,
       row_number() over (order by type desc, user, order) as new_order
    from records   
) y
    on x.id = y.id
when matched then
    update set order = new_order;

In the using clause, we are creating a new table with the expected order that we use to update the original table.

Answer 3

If you don't need new records be ordered from 1 I'd suggest this approach:

create table records (id int, [type] char(1), value int, [order] int, [user] int);
insert into records values
(1,'A',123,1,1),
(2,'B',231,2,1),
(3,'B',213,3,2),
(4,'A',222,4,2),
(5,'A',22,5,3),
(6,'B',11,6,4),
(7,'C',99,7,1);

create unique index ix1 on records([order]);
GO

I've created a unique index on [order] just to be sure it works properly.

declare @max_order int;
select @max_order=max([order]) from records;

with no as 
(
    select id, [type], value, [order], [user], 
           row_number() over (order by [user], [type] desc) as new_order
    from records
)
update rd
set    [order] = @max_order + no.new_order
from   records rd
join   no
on     no.id = rd.id;

GO

7 rows affected

select * from records;
GO

id | type | value | order | user
-: | :--- | ----: | ----: | ---:
 1 | A    |   123 |    10 |    1
 2 | B    |   231 |     9 |    1
 3 | B    |   213 |    11 |    2
 4 | A    |   222 |    12 |    2
 5 | A    |    22 |    13 |    3
 6 | B    |    11 |    14 |    4
 7 | C    |    99 |     8 |    1

If eventually you want [order] field starts with 1, you could get it by renumbering it again, but it requires another update of the whole table.

dbfiddle here

Answer 4

If you join the table with itself but in diffrent orders you can accomplish what you want. This query will do so for you.

;
WITH x AS(
SELECT      
    r1.[id],
    r1.[type],
    r1.[value],
    r1.[order],
    r2.[order] AS NewOrder,
    r1.[user]
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY [user] ORDER BY [order] DESC) AS OldSort
    FROM records) r1
INNER JOIN (SELECT 
    *,
    ROW_NUMBER() OVER (PARTITION BY [user] ORDER BY [TYPE] ASC) AS NewSort
FROM records) r2 ON r1.OldSort = r2.NewSort AND r1.[user] = r2.[user]
)
UPDATE records SET [order] = NewOrder
FROM records 
INNER JOIN x ON x.id = records.id

-

+----+------+-------+-------+----------+------+
| id | type | value | order | NewOrder | user |
+----+------+-------+-------+----------+------+
|  1 | A    |   123 |     1 |        7 |    1 |
|  4 | A    |   222 |     4 |        4 |    2 |
|  5 | A    |    22 |     5 |        5 |    3 |
|  6 | B    |    11 |     6 |        6 |    4 |
|  3 | B    |   213 |     3 |        3 |    2 |
|  2 | B    |   231 |     2 |        2 |    1 |
|  7 | C    |    99 |     7 |        1 |    1 |
+----+------+-------+-------+----------+------+

New DB Fiddle link: http://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=0d93ce38b18466f529f1799c183ade6b