Order by in the nested select statement

Question

I'm trying to use order by clause in the nested select statement but it is giving an error ora-00907 missing paranthesis

Select a.column_name,
       (Select upper(cmnt_type) AS CMNT_TYPE
        from t_tbm_appl_comment_2015
        where appl_mnm_id=a.appl_mnm_id
           AND rownum = 1
        order by cmnt_type desc)
from MD_OTHER_OBJECTS a

`AND rownum = 1 order by cmnt_type desc` You do not understand how ROWNUM works. And I think it is a typical **XY** problem. Your issue is with understanding ROWNUM rather than ORDER BY. — Lalit Kumar B, Oct 09 '15 at 08:30

score 1 · Answer 1 · answered Oct 09 '15 at 08:55

As Lalit has mentioned, your issue is that you are attempting to select a single row and then order it, which doesn't get you what you're after.

You'd have to rewrite your subquery to do the ordering first and then filter to just one row in an outer query, like so:

select cmnt_type
from   (select   upper(cmnt_type) AS CMNT_TYPE
        from     t_tbm_appl_comment_2015 tac
        where    tac.appl_mnm_id = a.appl_mnm_id
        order by cmnt_type desc)
where  rownum = 1

However, if you were to use that in the select list of your query, you'd end up with a an error of ORA-00904: "A"."APPL_MNM_ID": invalid identifier, due to the fact that correlated queries can only reference the outer query in the next level down.

Since it appears that you're trying to get the biggest upper(cmnt_type), why not use MAX() instead?

E.g.:

select a.column_name,
       (select max(upper(cmnt_type))
        from   t_tbm_appl_comment_2015 tac
        where  tac.appl_mnm_id = a.appl_mnm_id) cmnt_type
from   md_other_objects a;

score 0 · Answer 2 · answered Oct 09 '15 at 09:01

I don't know what exactly you want to achieve by using "RoWNUM =1 with Order by desc" clause, but the solution to remove ORA-00907 from this SQL is to remove Order by as you cant remove ROWNUM clause because otherwise it will give you ORA-01427 error. And also there is no point in adding Order by clause as you are using "Rownum = 1".

so your SQL should be like :

 Select a.column_name,
           (Select upper(cmnt_type) AS CMNT_TYPE
            from t_tbm_appl_comment_2015
            where appl_mnm_id=a.appl_mnm_id
               AND rownum = 1)
    from MD_OTHER_OBJECTS a;

Order by in the nested select statement

2 Answers2