Just for fun you can design a data model for which updating a row to rank N requires you to scan N rows and to update the value for just 1. Displaying the top N ranks also requires you to scan N rows. One weakness is that filtering down to just the Nth rank also requires you to scan N rows. Perhaps this could be useful if someone had many rows in their table and didn't want to potentially update millions of rows to perform a single rank change.
The key is to store a separate internal value for the rank that's different than the displayed rank but still in the correct order. Here I will store the internal priority as NUMERIC(10,6)
. For each update we can find the previous RAW_PRIORITY
value and subtract 0.000001
from it. That will preserve the order with just a single row update. With that precision end users change the ranks of items up to one million times before problems start happening. The precision and scale can be further increased depending on your requirements.
Define the table:
DROP TABLE IF EXISTS dbo.X_STORES;
CREATE TABLE dbo.X_STORES (STORE_NAME VARCHAR(100), RAW_PRIORITY NUMERIC(10,6));
INSERT INTO dbo.X_STORES VALUES
('Puma',1),
('Reebok',2),
('Dominos',3),
('Nike',4),
('NumeroUno',5)
CREATE INDEX X_STORES__PRIORITY ON dbo.X_STORES (RAW_PRIORITY) INCLUDE (STORE_NAME);
Depending on exact requirements it may make sense just to cluster the table on RAW_PRIORITY
. We also want a view to make it easy to display ranks in a human readable format as well as to make updates easier:
CREATE OR ALTER VIEW dbo.X_STORES_PRIORITY_ORDER
AS
SELECT STORE_NAME, ROW_NUMBER() OVER (ORDER BY RAW_PRIORITY) [PRIORITY], RAW_PRIORITY
FROM dbo.X_STORES;
With our starting date set Nike is 4th:
SELECT STORE_NAME, [PRIORITY]
FROM dbo.X_STORES_PRIORITY_ORDER
ORDER BY [PRIORITY];
Results:
╔════════════╦══════════╗
║ STORE_NAME ║ PRIORITY ║
╠════════════╬══════════╣
║ Puma ║ 1 ║
║ Reebok ║ 2 ║
║ Dominos ║ 3 ║
║ Nike ║ 4 ║
║ NumeroUno ║ 5 ║
╚════════════╩══════════╝
Let's change Nike to be ranked second:
UPDATE dbo.X_STORES
SET RAW_PRIORITY = (
SELECT v.RAW_PRIORITY - 0.000001
FROM dbo.X_STORES_PRIORITY_ORDER v
ORDER BY v.RAW_PRIORITY OFFSET 1 ROW FETCH NEXT 1 ROW ONLY
)
WHERE STORE_NAME = 'Nike';
Results:
╔════════════╦══════════╗
║ STORE_NAME ║ PRIORITY ║
╠════════════╬══════════╣
║ Puma ║ 1 ║
║ Nike ║ 2 ║
║ Reebok ║ 3 ║
║ Dominos ║ 4 ║
║ NumeroUno ║ 5 ║
╚════════════╩══════════╝
Now let's change Dominos to be first:
UPDATE dbo.X_STORES
SET RAW_PRIORITY = (
SELECT v.RAW_PRIORITY - 0.000001
FROM dbo.X_STORES_PRIORITY_ORDER v
ORDER BY v.RAW_PRIORITY OFFSET 0 ROW FETCH NEXT 1 ROW ONLY
)
WHERE STORE_NAME = 'Dominos';
Results:
╔════════════╦══════════╗
║ STORE_NAME ║ PRIORITY ║
╠════════════╬══════════╣
║ Dominos ║ 1 ║
║ Puma ║ 2 ║
║ Nike ║ 3 ║
║ Reebok ║ 4 ║
║ NumeroUno ║ 5 ║
╚════════════╩══════════╝