Join two tables with same # of row but sorted for NULL
- by VISQL
I need to join two tables with the same number of rows. Each table has 1 column. There is NO CONNECTING COLUMN to reference for a join. I need to join them side by side because each table was sorted separately so that numeric values are at the top in descinding order.
The Table Earners has income values from say 200K down to 0. I cannot just select using 2 cases, because then I will have my first row with Incomes above 100K, but the first 20 or so entries in the second row are NULL. I want the second row to also be sorted descending.
I looked up using ORDER BY within CASE but there is no such thing. I have tried to read about row_number() but none of the examples seem to match or make sense.
drop table #20plus
select
case
when Income >= 20000 AND Income < 100000
then Income end as 'mula'
into #20plus
from Earners
order by mula desc
drop table #100plus
select
case
when Income >= 100000
then Income end as 'dinero'
into #100plus
from Earners
order by dinero desc
Select A.dinero, B.mula
FROM #100plus as A JOIN #20plus as B
ON A.????? = B.?????
Since both A and B are sorted descending, moving all NULL to the bottom, what can I reference to join the two tables?
Previous output using one SELECT statement with 2 CASE statements
dinero mula
2.12688e+007 NULL
1.80031e+007 NULL
1.92415e+006 NULL
… …
NULL 93530.7
NULL 91000
NULL 84500
Desired output using one SELECT statement after creating two temp TABLES
dinero mula
2.12688e+007 93530.7
1.80031e+007 91000
1.92415e+006 84500
… 82500
NULL 82000
NULL …
NULL NULL
This is Microsoft SQL Server 2008. I'm super new to this, so please give an answer as clear and simplified as possible. Thank you.
