Set Order of top 10 Product manually in database

Question

I am creating a database for a mall. Client wants to set top 10 brands or stores manually. He wants to set order manually any time, for example:

store_Name     priority
Puma             1
Reebok           2
Dominos          3
Nike             4
NumeroUno        5

Product will be shows to user ordered by priority. Priority 1 will be shown at top.

My question is when I want to change the priority of store names like I want Nike to be shown at the top of search.

When I set the priority of Nike to 1 then automatically existing 1 position becomes 2 and 2 position becomes 3 and so on. How do I do this mechanism in SQL Server (stored procedure or Trigger)?

I am using SQL Server 2014 and my table has thousands of stores. I want 10 selectively store which is shown at the top of mobile app.

Answer 1

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 ║
╚════════════╩══════════╝

Answer 2

I have created a little script that I think does what you require.

I use row_number to maintain the order on the table. there are other ranking_functions too.

this might be an expensive operation, depending on your environment.

A table with the following values:

--store_Name     priority
--Puma             1
--Reebok           2
--Dominos          3
--Nike             4
--NumeroUno        5

SET NOCOUNT ON

-- I create a table for this example
-- store_name a varchar is not the best primary key 
-- priority is not a good column name 

DECLARE @STORE TABLE(
  store_name varchar(30) not null PRIMARY KEY CLUSTERED,
  [priority] smallint not null
  )


  -- adding the values according to the example
  INSERT INTO @STORE (store_name,[priority])
  SELECT 'Puma',1
  UNION ALL
  SELECT 'Reebok', 2
  UNION ALL
  SELECT 'Dominos', 3
  UNION ALL
  SELECT 'Nike', 4
  UNION ALL
  SELECT 'NumeroUno', 5

  -- showing what we have in the table
    SELECT store_name, [priority]
      FROM @STORE
    ORDER BY [priority]

-- setting priority one to nike
  UPDATE @STORE
  SET [priority] = 1
  WHERE store_name = 'Reebok'


-- maintaining the priority order on the table according to the example
    UPDATE @STORE
    SET S.[priority] =  R.THE_PRIORITY
    FROM @STORE S
    INNER JOIN (

                SELECT  THE_PRIORITY =ROW_NUMBER() OVER (ORDER BY [PRIORITY],CASE WHEN store_name = 'Reebok' THEN 0 ELSE 1 END)
                     ,store_name
                     ,[priority]
              FROM @STORE


    ) AS R ON S.store_name = R.store_name
    WHERE R.THE_PRIORITY IS NOT NULL

--showing the re-ordered table
    SELECT store_name, [priority]
      FROM @STORE
    ORDER BY [priority]