-1
select ( select min(first_col) from Data1 order by first_col DESC) as AB,
       max(second_col)
from Data1;

I am getting missing right parenthesis error in oracle sql developer and I dont know why

ran it didn't work as expected error seems to be occuring when I am uisng order by in subquery. if I run that subquery independently, it works fine so dont know why sql developer is throwing error

yash
  • 1
  • 1
  • 3
    I'd start with removing the subquery's ORDER BY. (It makes no difference at all.) – jarlh Feb 13 '23 at 08:28
  • 1
    What is the purpose of `order by` in the subquery that returns a single row? – astentx Feb 13 '23 at 08:37
  • 1
    I think you are looking for something like : `select min(first_col), max(second_col) from Data1;` – SelVazi Feb 13 '23 at 08:39
  • 1
    You can't (usually) have an order-by in a subquery, which is directly causing this error. Why are you using a subquery at all? – Alex Poole Feb 13 '23 at 08:40
  • 1
    Look at the subquery alone: `select min(first_col) from Data1 order by first_col DESC`. With `MIN(first_col)` you aggregate your rows into one single result row containing the minimum value for `first_col`. What then is `ORDER BY first_col DESC` supposed to do? You want to sort one row? And `first_col` doesn't even exist in this result set, because you aggregated your rows. Only the `MIN(first_col)` exists. You could order by this expression, but, well, sorting one row still doesn't make sense. – Thorsten Kettner Feb 13 '23 at 11:17
  • The error is a SQL syntax error from the database, and has nothing to do with SQL Developer. – William Robertson Feb 13 '23 at 23:12

2 Answers2

2

You can't usually have an order-by clause in a subquery, because it's meaningless, though it is generally allowed (but ignored) in an inline view. The order of the results is irrelevant to the outer query (with the exception of rownum handling). When used in a select list as you have it here it has to be a scalar subquery returning exactly one value, so ordering that single value would be pointless, if it were allowed.

The parser is expecting to see a ) instead of that order by, so the error does make some sense, once you know what it wrong; but it doesn't really help you narrow it down if that's all you see.

It's perhaps not obvious that this restriction exists from the documentation, but it is mentioned in Oracle support document 731577.1:

Getting ORA-00907: missing right parenthesis when using an ORDER BY clause in a subquery. When the ORDER BY clause is removed the query runs without error.

...
This is expected behavior per Bug 4944718 ORDER BY in a subquery shouldn't work, since the order of the rows is passed to the outer query and has no impact.

From your question you already know that the order by is causing the issue, so you can just remove that clause. It isn't obvious why you have a subquery there at all.

APC
  • 144,005
  • 19
  • 170
  • 281
Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • I know that query as whole is meaning less. I was trying to make a query for having two columns with different sorting as shown in answer like col1 in ascending col2 in descending order and this will definaetly alter the result but such been asked in interview but been hitting problems so i though let me first write some simple query but thats giving error as you seen. BTW why cant I use order by its logical but meaningless. even for my problem I use rownum and thats giving error too – yash Feb 14 '23 at 13:37
0

Actually, (select min(first_col) from Data1 order by first_col DESC) is not a subquery, but what is called "inline view". Think that selecting only a min(first_col) will fetch only one row. So, does it make sense adding that order by? I think not. Then, why not just simplify the whole thing? I'd write>:

select min(first_col), max(second_col) from data1;

For I think you wish to fetch the max(first_col) and min(second_col) and that would be all. Or is it that you need something else? If so, then please explain what you mean to do, giving also the structure for the table(s).

APC
  • 144,005
  • 19
  • 170
  • 281
Bogdan Dincescu
  • 683
  • 6
  • 4
    In this context it is a [scalar subquery expression](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Scalar-Subquery-Expressions.html#GUID-475D80C3-C873-4475-AB1A-8837C5CF8CE4), not an inline view. – Alex Poole Feb 13 '23 at 09:02
  • I get that but my issue was why cant use order by in subquery even if its meaninglesss. earlier I used to work in paid version there I didn get such issue – yash Feb 14 '23 at 13:50
  • So, why use order by in subquery? Just to force Oracle perform a sort according to some arbitrary criteria, which is definitely detrimental to performance. Usually by subquery, Oracle developers understand something like: in (select ... from ... where ...) or exists (select 1 from ... where ...). In which case an order by would simply make no sense. If Oracle does not allow an order by clause in a subquery, then I think it proves to be smart enough not to waste resources to perform a useless sort, and, at the same time, not as tolerant as to leave that there and not mind it being there. – Bogdan Dincescu Feb 14 '23 at 15:35