MYSQL priority or rank based values exchange inside a group

I am trying to figure out how to write a functiom, which would run trough my dataset and fullfill several conditions.

Here is a quick look at the dataset

enter image description here

We have same Items with different quantities on different locations with different priorities.

The trick is to find a location with the lowest priority and add that quantity or just 1 to the location with the highest priority for the same item. Then move on the the second lowest priority and add that to the second largest and so on untill all pairs in the same item_no group are done, and if there is an uneven number of locations leave the last one allone, move on to the next item__no.

Priorities go from 1-26 , 1 being the highest and 26 the lowest.

for starters I just wanted to write something that would at least get me the result for the first group. I have item_no 100115 and to locations P26 and P30, P30 needs to receive that item from P26 because it has a higher priority

The desired output needs to be in the following format

in_location | from_location | item_no | quantity

I wrote this

SELECT in_loc, from_loc, item_no, quantity FROM
(
SELECT
(SELECT Store_no from sep  WHERE Priority = 15) AS in_loc, 
Store_no AS from_loc, 
Item_no, 
Stock_Quantity AS quantity FROM sep
WHERE Item_no = 100115) AS P
WHERE in_loc <> from_loc

which got me the desired result for the first item

enter image description here

I am trying to figure out how to go about the complete function.

Like:

Creating and joining or self joining a copy of the table where i could say just a.store_no <> b.store_no

Do I need to state cases where one CASE is even number of locations in a Group and the other is uneven.

How do I go about the actual exchange , Do I do a COUNT of the number of locations for all items in a group and divide in pairs, do I pair GREATEST with the LEAST then move on to the second GREATEST or somehow use TOP nad BOTTOM with offset.

Maybe I need to define some local variables?

Edit

Sample table script

CREATE TABLE sep (
    id INT PRIMARY KEY NOT NULL,
    item_no VARCHAR(12),
    store_no VARCHAR(4),
    quantity INT,
    priority INT);
    
INSERT INTO sep (id, item_no, store_no, quantity, priority)
VALUES
(1, 100115, 'P026', 1, 15),
(2, 100115, 'P030', 1, 19),
(3, 100116, 'P015', 3, 14),
(4, 100116, 'P017', 1, 2),
(5, 100116, 'P002', 1, 11),
(6, 100117, 'P021', 1, 9),
(7, 100117, 'P033', 3, 20),
(8, 100117, 'P008', 1, 22),
(9, 100117, 'P007', 5, 5);

Also script for output if needed

CREATE TABLE output (
    in_location VARCHAR(4),
    from_location VARCHAR(4),
    item_no VARCHAR(12),
    quantity INT);

Update

Thanks for the response, just a little tweak needed with output now. This is the output from the answer

enter image description here

and I actually need this

enter image description here

Explanation about the output: we are looking at a large retailer, after doing inventory in some store locations it was found that certain items are unpaired when in fact they should be sold in pairs.

If you look at the first image in the post you can see that an item has an inventory multiple is x2 and quantity on location is 1 or 3 or whatever when in fact it should always be in pair. That is why I am trying to find storre_locations with lower priority which can give at least 1 item to a store_location with higher priority if they have the same unpaired item…

That’s why in my output I need to see that

Pxx(in_location with higher priority) Pxx(from_location with lower priority) Item_no(which item) quantity(receives how many/ 1 is enough)

Answer

Does this query gives the output which you need in?

WITH cte AS ( SELECT id, item_no, store_no, quantity, priority, 
                     LEAST(ROW_NUMBER() OVER (PARTITION BY item_no ORDER BY priority),
                           1 + COUNT(*) OVER (PARTITION BY item_no) - ROW_NUMBER() OVER (PARTITION BY item_no ORDER BY priority)) pair 
               FROM sep )
SELECT id, item_no, store_no, quantity, priority,
       CASE WHEN priority = MIN(priority) OVER (PARTITION BY item_no, pair)
            THEN quantity + 1
            ELSE quantity - 1 
            END new_quantity
FROM cte
ORDER BY item_no, priority

fiddle

Leave a Reply

Your email address will not be published. Required fields are marked *