How can I join two SELECT statements?

Question

select * 
From
(
    select * 
    from order 
    order by creationtime desc
) 
where rownum=1 and creationtime='12-feb-2010';

and

select * 
from
(
    select * 
    from 
    order by rate desc
) 
where rownum<=2 and creationtim='12-dec-2011';

I want to join these two SELECT queries, using JOIN. Both SELECT queries query from same table. I do not want use UNION.

How can I achieve this?

Answer 1

It is hard to tell from your question what all should be in the "ON" clause below since you didn't indicate the primary key, but this should give you the idea of what you need to do.

select * From
(select * from order order by creationtime desc) A 
INNER JOIN (select * from order by rate desc) B
ON A.rownum = B.rownum
where A.rownum=1 and A.creationtime='12-feb-2010' 
AND B.rownum<=2 and B.creationtim='12-dec-2011'