CODEWITHSUNDEEP
sqloracle-databasesql-order-by
Oleksandr Samsonov
Oleksandr Samsonov

Reputation: 1097

order by asc and desc

I need select some rows from table but that rows have to be ordered by ROWNUMBER asc and if ROWNUMBER is null i must order by created desc;

This is my sql query without order by condition;

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 <> 'DELETED'
                        AND GR_USR.STATE <> 'DELETED'
                        AND MSG.STATE <> '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 <= 3
 ORDER BY

Oracle 10g;

Upvotes: 0

Views: 311

Answers (1)

MathCurious
MathCurious

Reputation: 174

  1. it is unclear to what created desc you referred due to the fact that you've specified FIN_CGU.*, and NOT the full column list.
  2. if you were referring to the SELECT MSG.GROUP_ID AS R_GR, MAX(MSG.CREATED) AS M_DATE and created in that list then you need to bring that column up from your dynamic view that are referenced as RES (and inside reference as AGGR_RES) to the top list of columns.

  3. once you have that column in the list of columns it appears that your inner query should provide ordering; so you can do either by case or nvl as :

    order by case when ROWNUMBER is not null then ROWNUMBER else created end

  4. you cann't flip-flop asc|desc on the same column list dynamically on static query. you can do that in dynamic SQL. you can change ordering column though ( shown )

Upvotes: 1

Related Questions