Solved

Join two tables in descending sort order

Posted on 2013-05-15
10
319 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 31

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
 

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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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 31

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now