Reputation: 1097
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
Reputation: 174
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
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