title: order by asc and desc
body: <p>I need select some rows from table but that rows have to be ordered by <code>ROWNUMBER asc</code> and if <code>ROWNUMBER</code> is null i must order by <code>created desc</code>;</p>
  
  <p>This is my sql query without order by condition;</p>
  
  <pre><code>SELECT FIN_CGU.*, RES.ROWNUMBER 
    FROM COMM$GROUP_USER FIN_CGU
    FULL JOIN (SELECT AGGR_RES.R_GR AS TOT_GR,
                      ROW_NUMBER() OVER(ORDER BY AGGR_RES.M_DATE DESC) AS ROWNUMBER
                 FROM (SELECT MSG.GROUP_ID AS R_GR, MAX(MSG.CREATED) AS M_DATE
                         FROM COMM$MESSAGE MSG
                         JOIN COMM$GROUP_USER GR_USR
                           ON GR_USR.GROUP_ID = MSG.GROUP_ID
                         JOIN COMM$GROUP GR
                           ON GR.ID = MSG.GROUP_ID
                        WHERE GR.STATE &lt;&gt; 'DELETED'
                          AND GR_USR.STATE &lt;&gt; 'DELETED'
                          AND MSG.STATE &lt;&gt; 'DELETED'
                          AND GR_USR.USER_ID = 9172771513163989084
                        GROUP BY MSG.GROUP_ID) AGGR_RES
                ORDER BY AGGR_RES.M_DATE DESC) RES
      ON FIN_CGU.GROUP_ID = RES.TOT_GR
    JOIN COMM$GROUP CG
      ON FIN_CGU.GROUP_ID = CG.ID
   WHERE (ROWNUMBER IS NULL OR ROWNUMBER BETWEEN 1 AND 3)
     AND FIN_CGU.USER_ID = 9172771513163989084
     AND RES.ROWNUM &lt;= 3
   ORDER BY
  </code></pre>
  
  <p>Oracle 10g;</p>