Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 335
  • Last Modified:

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
0
doolinn
Asked:
doolinn
  • 3
  • 2
  • 2
  • +2
2 Solutions
 
SStoryCommented:
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
 
x-menIT 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
 
awking00Commented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
doolinnAuthor Commented:
Thanks for your feedback - As a follow up:

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:
see the link  

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.  

See the link

http://sqlfiddle.com/#!2/5a178/1
0
 
PortletPaulCommented:
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
 
SStoryCommented:
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
 
PortletPaulCommented:
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
 
awking00Commented:
doolinn,
What dbms are you actually using?
0
 
doolinnAuthor 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
 
PortletPaulCommented:
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now