CODEWITHSUNDEEP
oracle-databasesql-order-bygreatest-n-per-groupsubquery
YvesR
YvesR

Reputation: 6222

Oracle Query Subselect with order by

I am normally using MS SQL and am a total rookie with oracle.

I get an oracle driver problem when I use the ORDER BY statement in my subquery.

Example (my real statement is much more complex but I doubt it matters to my problem - I can post it if needed):

SELECT col1
     , col2
     , (SELECT colsub FROM subtbl WHERE idsub = tbl.id AND ROWNUM=1 ORDER BY coldate) col3
FROM tbl

If I do such a construct I get an odbc driver error: ORA-00907: Right bracket is missing (translated from german, so bracket might be other word :)).

If I remove the ORDER BY coldate everything works fine. I couldn't find any reason why, so what do I wrong?

Upvotes: 1

Views: 323

Answers (3)

Mike Meyers
Mike Meyers

Reputation: 2895

It doesn't make any sense to write the ROWNUM and the ORDER BY this way since the ORDER BY is evaluated after the WHERE clause, meaning that it has no effect in this case. An example is given in this question.

This also gets a little more complicated because it is hard to join a sub-query back to the main query if it is nested too deeply.

The query below won't necessarily work because you can't join between tbl and subtbl in this way. 

SELECT 
  col1,
  col2,
  (
    select colsub
    from  (
      SELECT colsub 
       FROM subtbl 
       WHERE idsub = tbl.id
       order by coldate
    )
    where rownum = 1
  ) as col3
FROM tbl

So you'll need to use some sort of analytic function as shown in the example below:

SELECT 
  col1,
  col2,
  (SELECT max(colsub) keep (dense_rank first order by coldate) as colsub
   FROM subtbl 
   WHERE idsub = tbl.id
   group by idsub
) col3
FROM tbl

The FIRST analytic function is more complicated than it needs to be but it will get the job done.

Another option would be to use the ROW_NUMBER analytic function which would also solve the problem.

SELECT 
  col1,
  col2,
  (select colsub
   from (
     SELECT 
       idsub, 
       row_number() over (partition by idsub order by coldate) as rown,
       colsub
   FROM subtbl a 
   ) a
   WHERE a.idsub = tbl.id
   and a.rown = 1
) col3
FROM tbl

Upvotes: 2

David Aldridge
David Aldridge

Reputation: 52346

try:

select
  col1,
  col2,
  colsub
from(
  select
    col1   ,
    col2   ,
    coldate,
    max(coldate) over (partition by st.idsub) max_coldate
  from
    tbl    t,
    subtbl st
  where
    st.idsub = t.id)
where
  coldate = max_coldate

Upvotes: 2

Rene
Rene

Reputation: 10541

What you are doing wrong is clear. You are using an order by in a sub-query. It does not make any sense using an order by in a sub-query so why would you want to do that? Also you are using an order by on a sub-query that always returns 1 row. That also does not make any sense.

If you want the query result to be sorted use an order by at the highest level.

Upvotes: 2

Related Questions