How to print two columns in ascending order from 2 tables without common column-CodePudding

this question was asked to me in interview i was not able give a answer i tried every solution internet did not gave desired output in oracle sql input table table A has ID column and table B has Value columns ``table A` ID(table A) Value(table B)
1 E
2 C
3 B
4 A
5 D

output table wants

ID Value

1 A
2 B
3 C
4 D
5 E

CodePudding user response：

You can both order your numbers table and the letters table and then join the numbers table with the letters table on the row number of the letter table:

SELECT numbers.id, letters.value FROM
(SELECT id
FROM tableA) numbers
JOIN 
(SELECT ROW_NUMBER() OVER(ORDER BY value) id, value
FROM tableB ) letters
ON numbers.id = letters.id
ORDER BY numbers.id, letters.id

CodePudding user response：

This simply seems a ROW_NUMBER window function -

SELECT ROW_NUMBER() OVER(ORDER BY value) id, value
  FROM your_table
 ORDER BY value;

CodePudding user response：

You can match the tables by using ASCII() function as ordering by one of the id or value columns while using a CROSS JOIN such as

SELECT id, value
  FROM tableA 
 CROSS JOIN tableB
 WHERE ASCII(value)-64 = id  
 ORDER BY id

Demo