I have two views view1 and view2 I want to join the data from both views. The data example is the following : view1

View2 data:

So the desired result which I want is the following:

CodePudding user response：

If you're combining the results and want to align data from the two views in counts order, you can generate a nominal ordinal value for each row in each view, for example with the row_number() function:

select v.old_numbers, v.counts,
  row_number() over (order by v.counts, v.old_numbers)
from view1 v

and something similar for the other view; then use those as inline views or CTEs, and perform a full outer join based on that ordinal value:

with v1 (old_numbers, counts, rn) as (
  select v.old_numbers, v.counts,
    row_number() over (order by v.counts, v.old_numbers)
  from view1 v
),
v2 (numbers, counts, rn) as (
  select v.numbers, v.counts,
    row_number() over (order by v.counts, v.numbers)
  from view2 v
)
select v1.old_numbers, v1.counts, v2.numbers, v2.counts
from v1
full outer join v2 on v2.rn = v1.rn
order by coalesce(v1.rn, v2.rn)

db<>fiddle