Sort By Child Table But Maintain Parent Table Order

-2 votes
2 answers
85 views
                          I am currently trying to sort the results of two tables in such a way that the primary sort field is one in the child table but would like to keep the parent table results from being moved out of sequence.

An example would be the following tables (using Ms SQL Server) :

    CREATE TABLE tier_a(
       idno INTEGER NOT NULL,
       name VARCHAR(10) NOT NULL
    );

    CREATE TABLE tier_b(
       idno INTEGER NOT NULL,
       tier_a_idno INTEGER NOT NULL,
       name VARCHAR(10) NOT NULL
    );

    INSERT INTO tier_a VALUES(3, 'C');
    INSERT INTO tier_a VALUES(2, 'B');
    INSERT INTO tier_a VALUES(1, 'A');

    INSERT INTO tier_b VALUES(1, 1, 'A');
    INSERT INTO tier_b VALUES(4, 2, 'C');
    INSERT INTO tier_b VALUES(2, 1, 'B');
    INSERT INTO tier_b VALUES(5, 3, 'A');
    INSERT INTO tier_b VALUES(3, 1, 'C');

If I only sort by the child table :

    SELECT tier_a.name AS tier_a_name, tier_b.name AS tier_b_name
    FROM tier_a
    JOIN tier_b ON tier_b.tier_a_idno = tier_a.idno
    ORDER BY tier_b_name ASC

I get what I expect :

Ascending by child

    tier_a_name | tier_b_name
    C           | A
    A           | A
    A           | B
    A           | C
    B           | C

Descending by child

    tier_a_name | tier_b_name
    B           | C
    A           | C
    A           | B
    C           | A
    A           | A

But what I am trying to achieve is to still keep the ordering of the parent table :

Ascending by child

    tier_a_name | tier_b_name
    A           | A
    A           | B
    A           | C
    C           | A
    B           | C

Descending by child

    tier_a_name | tier_b_name
    A           | C
    A           | B
    A           | A
    B           | C
    C           | A

I can simulate it with a terrible manual ORDER BY :

Ascending by child

    SELECT tier_a.name AS tier_a_name, tier_b.name AS tier_b_name
    FROM tier_a
    JOIN tier_b ON tier_b.tier_a_idno = tier_a.idno
    ORDER BY
       CASE
          -- Parent of the Lowest tier_b name (A)
          WHEN tier_a.idno = 1 THEN 1
          -- Parent of the Lowest tier_b name (A) but Higher tier_a idno
          WHEN tier_a.idno = 3 THEN 2
          -- Parent of the Highest tier_b name (C)
          WHEN tier_a.idno = 2 THEN 3
       END,
       tier_b_name ASC

Descending by child

    SELECT tier_a.name AS tier_a_name, tier_b.name AS tier_b_name
    FROM tier_a
    JOIN tier_b ON tier_b.tier_a_idno = tier_a.idno
    ORDER BY
       CASE
          -- Parent of the Highest tier_b name (C)
          WHEN tier_a.idno = 1 THEN 1
          -- Parent of the Highest tier_b name (C) but Higher tier_a idno
          WHEN tier_a.idno = 2 THEN 2
          -- Parent of the Lowestest tier_b name (A)
          WHEN tier_a.idno = 3 THEN 3
       END,
       tier_b_name DESC

                        
Asked by TheLovelySausage (222 rep)
Jul 22, 2024, 04:13 PM
Last activity: Jul 22, 2024, 05:13 PM
Sort By Child Table But Maintain Parent Table Order

Related Questions
