按子表排序但保持父表顺序

我目前正在尝试对两个表的结果进行排序，使得主排序字段是子表中的一个，但希望保持父表结果不被移出序列。

以下表格就是一个例子（使用 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');

如果我仅按子表排序：

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

我得到了我期望的：

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

但我想要实现的是仍然保持父表的顺序：

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

我可以用一本糟糕的手册来模拟它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