I would like to preserve the column order from a subquery that uses UNION to gather column values (refining the question I posed here SQLite alternative to transposition / sub-select returns N columns - expected 1). The query below sorts numerically in ascending order of the keys, and I can't figure out a way to preserve their column order from the Keys table without introducing another attribute.

A minimal example:

CREATE TABLE Keys(primary_id, id1, id2, id3);
CREATE TABLE Attr(id, attr1, attr2);

INSERT INTO Keys VALUES(1, 7, 2, 5);
INSERT INTO Keys VALUES(2, 6, 1, 3);
INSERT INTO Keys VALUES(3, 4, 2, 7);
INSERT INTO Attr VALUES(1, "a", "b");
INSERT INTO Attr VALUES(2, "c", "d");
INSERT INTO Attr VALUES(3, "e", "f");
INSERT INTO Attr VALUES(4, "g", "h");
INSERT INTO Attr VALUES(5, "i", "j");
INSERT INTO Attr VALUES(6, "k", "l");
INSERT INTO Attr VALUES(7, "m", "n");

SELECT *
FROM Attr
WHERE Attr.id IN (SELECT primary_id FROM Keys WHERE Keys.primary_id=2
                  UNION
                  SELECT id1 FROM Keys WHERE Keys.primary_id=2
                  UNION
                  SELECT id2 FROM Keys WHERE Keys.primary_id=2
                  UNION
                  SELECT id3 FROM Keys WHERE Keys.primary_id=2);

1|a|b
2|c|d
3|e|f
6|k|l

Desired output preserving the ordering of primary_id, id1, id2, id3:

2|c|d
6|k|l
1|a|b
3|e|f

The subquery by itself I could sort by creating another attribute:

SELECT primary_id, 1 as sort FROM Keys WHERE Keys.primary_id = 2
UNION
SELECT id1, 2 as sort FROM Keys WHERE Keys.primary_id = 2
UNION
SELECT id2, 3 as sort FROM Keys WHERE Keys.primary_id = 2
UNION
SELECT id3, 4 as sort FROM Keys WHERE Keys.primary_id = 2
ORDER BY sort

But the outer query expects only 1 column, so this can't be a solution. Thank you for your ideas!

CodePudding user response：

Use a CTE that returns the 2 columns and join it to the table instead of using the operator IN.
Then you can use the column sort to sort the results:

WITH cte AS (
  SELECT primary_id AS id, 1 AS sort FROM Keys WHERE Keys.primary_id = 2
  UNION 
  SELECT id1, 2 AS sort FROM Keys WHERE Keys.primary_id = 2
  UNION 
  SELECT id2, 3 AS sort FROM Keys WHERE Keys.primary_id = 2
  UNION 
  SELECT id3, 4 AS sort FROM Keys WHERE Keys.primary_id = 2
)
SELECT DISTINCT a.*
FROM Attr a INNER JOIN cte c
ON c.id = a.id
ORDER BY c.sort;

See the demo.

CodePudding user response：

You can join on an IN.
And order by a CASE.

SELECT a.*
FROM Attr a
INNER JOIN Keys k
  ON k.primary_id = 2
 AND a.id IN (k.primary_id, k.id1, k.id2, k.id3)
ORDER BY 
 CASE a.id 
 WHEN k.primary_id THEN 0
 WHEN k.id1 THEN 1
 WHEN k.id2 THEN 2
 WHEN k.id3 THEN 3
 ELSE 9
 END

Demo on db<>fiddle here