Expiring Todayâ€”Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Join two tables in descending sort order

Posted on 2013-05-15
Medium Priority
329 Views
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
Question by:doolinn
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3
• 2
• 2
• +2

LVL 25

Expert Comment

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

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

awking00 earned 1600 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

ID: 39169824

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

LVL 49

Expert Comment

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

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 49

Assisted Solution

PortletPaul earned 400 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

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

Author Comment

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 49

Expert Comment

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

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.â€‹
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (httpsâ€¦
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrasâ€¦
###### Suggested Courses
Course of the Month11 days, 2 hours left to enroll