SQL | Shuffle order set by row_number based on another table

All we need is an easy explanation of the problem, so here it is.

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

Table A

Name
A
B
C
D

Table B

name manual_order_no
C 1
D 3

Expected result using table B

name final_order_no
C 1
A 2
D 3
B 4

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

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

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

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

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

Leave a Reply