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

    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 😮

    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
    


  • 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




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2