Solved

Join two tables in descending sort order

Posted on 2013-05-15
10
323 Views
Last Modified: 2013-05-16
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
Comment
Question by:doolinn
  • 3
  • 2
  • 2
  • +2
10 Comments
 
LVL 25

Expert Comment

by:SStory
ID: 39168229
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
 
LVL 18

Expert Comment

by:x-men
ID: 39168257
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
 
LVL 32

Accepted Solution

by:
awking00 earned 400 total points
ID: 39168373
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
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 

Author Comment

by:doolinn
ID: 39169824
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39170204
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
 
LVL 25

Expert Comment

by:SStory
ID: 39171125
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
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 100 total points
ID: 39171162
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
 
LVL 32

Expert Comment

by:awking00
ID: 39171413
doolinn,
What dbms are you actually using?
0
 

Author Comment

by:doolinn
ID: 39171424
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39171516
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

Technology Partners: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.

713 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question