By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE T1
(
Col1 VARCHAR(45),
Col2 VARCHAR(45)
);
CREATE TABLE T2
(
Col1 VARCHAR(45),
Col2 VARCHAR(45)
);
INSERT INTO T1 (Col1, Col2) SELECT 'Val1', 'Val2';
INSERT INTO T2 (Col1, Col2) SELECT 'Val11', 'Val22';
WITH CTE AS
(
SELECT Col1, Col2, 'T1' [T1.T2]
FROM T1
UNION
SELECT Col1, Col2, 'T2'
FROM T2
)
SELECT *
FROM CTE
ORDER BY [T1.T2] DESC;
| Col1 | Col2 | T1.T2 |
|---|---|---|
| Val11 | Val22 | T2 |
| Val1 | Val2 | T1 |