Oracle - join 2 selects side by side?

Hi, I'm not an SQL guru and this seemingly simple problem made me quite much thinking and searching. That's why I decided to share it.

The problem: I have a table (eg foo) with many columns, for me only two were important:

• name (varchar2, unique)
• code (varchar2, unique)

and I had two selects:

• select name, code from foo order by name
• select code, name from foo order by code

Got it? The only difference is the sorting! Now I needed to join (NOT union) these two selects in a way to prefer the order of both selects. Easy? Give it a try... :)

The solution was Oracle's 'rownum':

SELECT c1_name, c1_code, c2_code, c2_name
   FROM
    (SELECT ROWNUM AS s, c1_name, c1_code
       FROM (SELECT name AS c1_name, code_id AS c1_code 
               FROM foo ORDER BY c1_name)) t1
LEFT JOIN
    (SELECT ROWNUM AS s, c2_code, c2_name
       FROM (SELECT code AS c2_code,name AS c2_name 
               FROM foo ORDER BY c2_code)) t2
ON t1.s = t2.s

I had to wrap the two selects with another select with rownum, when rownum was directly in the first select, the ordering scrambled it. From here you need to only join the two tables using rownums, do it as you wish, I used left join but probably there could be better ways.