SQL | Shuffle order set by row_number based on another table

I need to manually shuffle the order set by row_number in a table based order mentioned in the different table –

Table A

Table B

Expected result using table B

I am trying with below query but it doesn’t fully work ; banging my head to see what am I missing :O

with t1 as (select name,
                   ROW_NUMBER() OVER (ORDER BY name asc) as default_order_no
            from A),
     t2 as (
         select t1.*,
                ROW_NUMBER() OVER (ORDER BY coalesce(B.manual_order_no, default_order_no)) as order_no
         from B
                  right join t1 on B.name = t1.name)
select t2.name, row_number() OVER (ORDER BY order_no asc) as final_order_no
from t2

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

Method 1

WITH RECURSIVE
nums AS ( SELECT 1 manual_order_no
          UNION ALL
          SELECT manual_order_no + 1 
          FROM nums 
          WHERE manual_order_no < ( SELECT COUNT(*) 
                                    FROM A )
),
A1 AS ( 
    SELECT name, ROW_NUMBER() OVER (ORDER BY name) rn
    FROM A
    LEFT JOIN B USING (name)
    WHERE B.manual_order_no IS NULL
), 
nums1 AS (
    SELECT manual_order_no, ROW_NUMBER() OVER (ORDER BY name) rn
    FROM nums
    LEFT JOIN B USING (manual_order_no)
    WHERE B.manual_order_no IS NULL
)
SELECT name, manual_order_no
FROM B
UNION ALL
SELECT A1.name, nums1.manual_order_no
FROM A1
JOIN nums1 USING (rn)
ORDER BY manual_order_no

and

INSERT INTO B (name, manual_order_no)
WITH RECURSIVE
nums AS ( SELECT 1 manual_order_no
          UNION ALL
          SELECT manual_order_no + 1 
          FROM nums 
          WHERE manual_order_no < ( SELECT COUNT(*) 
                                    FROM A )
),
A1 AS ( 
    SELECT name, ROW_NUMBER() OVER (ORDER BY name) rn
    FROM A
    LEFT JOIN B USING (name)
    WHERE B.manual_order_no IS NULL
), 
nums1 AS (
    SELECT manual_order_no, ROW_NUMBER() OVER (ORDER BY name) rn
    FROM nums
    LEFT JOIN B USING (manual_order_no)
    WHERE B.manual_order_no IS NULL
)
SELECT A1.name, nums1.manual_order_no
FROM A1
JOIN nums1 USING (rn)

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=a767751d4f21961ba3946a457f5baa40

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0