Join two tables in descending sort order

I am trying to join two tables as per the following example:-

Table 1:-

ID   Ref    Amount
1     A       100
2     B       105
3     C       110

Table 2:-

ID   Ref    Amount
10     AA       120
11     BB       121
12     C C      118
13     DD      105

...to produce a combined/joined result that has the data on table 1 sorted descending according to Amount side-by-side with the data on table 2 sorted descending according to Amount.

ie in the above example I would expect the result to return:-

ID1  Ref1  Amount1    ID2   Ref2   Amount 2
3     C        110              11     BB      121
2     B        105              10     AA      120
1     C        100              12     CC       118
13     DD      105

Note neither table is guaranteed to have the same amount of records - 1 may have more than 2 and vice versa.

Thanks
Who is Participating?

Commented:
select
x.id as id1, x.ref as ref1, x.amount as amount1,
y.id as id2, y.ref as ref2, y.amount as amount2 from
(select id, ref, amount,
row_number() over (order by amount desc) rn
from tab1) as x
full outer join
(select id, ref, amount,
row_number() over (order by amount desc) rn
from tab2) as y
on x.rn = y.rn
;
0

Commented:
Assuming table 1 is the one that always has values,

SELECT ID1, Ref1,Amount1, ID2, Ref2, Amount 2
FROM Table1 LEFT JOIN ID1=ID2
ORDER BY Amount1 DESC, Amount2 DESC

This will always match table1 to table2 where ID's are equal and show table 1 rows regardless of whether there are matches in table 2

If you only want machines, change "LEFT JOIN" to "INNER JOIN"
0

IT super heroCommented:
select distinct t1.ID,t1.Ref,t1.Ammount,t2.ID,t2.Ref,t2Amount from t1 cross join t2 ORDER BY t1.amount desc, t2.amount desc
0

Author Commented:

SSTory: Your solution assumes IDs are equal in t1 and t2 - if you look at the example table you will see they are different.

x-men: I cannot get this to work - it returns 4x as many records of the first table:

http://www.sqlfiddle.com/#!2/6d38f/3/0

where i have created the tables and your proposed solution.

awking00: I get an error message when I try to run this.

http://sqlfiddle.com/#!2/5a178/1
0

Commented:
your choice of topics tells us this is an MS SQL question, but your sqlfiddles immediately above are both using MySQL

try this one (using awking00's use of row_number) and full outer join:
http://sqlfiddle.com/#!3/10feb/2

for what it's worth you most likely do need a full outer join
and if the question does relate to MySQL then you cannot use row_number()

a MySQL equivalent to row_number() requires the use of @variables
please lets us know if the question is for MySQL
0

Commented:
If nothing allow them to be joined, there is no way to join them unless you want some cross product. I can't see where that would be useful. That is basically every possible combination.
Please tell us how they relate to each other.
0

Commented:
that's why awking00 has offered the row_number() approach

the generated row number become the method by which the 2 independent resultsets can be aligned (technically a join, but I'd think of it more as alignment) so both rows 1 align, rows 2 align etc. Then, by using full outer join it will not matter which has more rows than the other as all rows will be returned. Thus, 2 disparate queries "joined" via a calculated property (row number).

(And it avoids a "cross product" (cartesian product) that a cross join would produce.)
0

Commented:
doolinn,
What dbms are you actually using?
0

Author Commented:
Thanks Portlet Paul - that is correct - the "join" is essentially from the row ranking/number.

+ The reference to MySql was oversight - intent was always MS SQL. I've allocated 100 points for clarifying this.
0

Commented:
thank you, happy to help out

I felt awking00's solution was perfect for the  given scenario and became surprised when your sqlfiddle failed. Need to watch that dbms selector when using that (excellent) site.
I've fallen into the same trap a few times myself. :)

Cheers,
Paul
0