Error in WHERE ...= (SELECT ... ORDER ...)

[dnz63]

Hi All,
ihave a simple query to db

select obj#, owner#, ctime from sys.obj$ o1
where obj# = (
select obj# from sys.obj$ o2
where o1.owner# = o2.owner#
and rownum = 1
-- order by ctime desc
)

but after remove commect on ORDER BY line i have error
ORA-00907 missing right parenthesis.
Can you help me to resolve this error ?

[DaPi]

The order by must be after the closing parenthesis.

[DaPi]

Or is this what you want:

select  obj#, owner#, ctime 
from
(select obj#, owner#, ctime, 
 max(ctime) over (partition by owner#) mct 
 from   sys.obj$)
where ctime=mct

[Scorby]

The original problem may be that the tool you are using to execute the query has removed all the carriage returns. The SQL has ended up as:

select .. and rownum = 1 -- order by ctime desc )

Obviously this comments the closing bracket as well as the order by.

[DaPi]

No Scorby, OP said:

but after remove commect on ORDER BY line i have error
ORA-00907 missing right parenthesis.

[Scorby]

Must have removed the bracket as well then.

[dnz63]

This query select first object (by ctime field) to each owner.
Problem not in -- and carrige return.
For example:
i have
obj# owner# ctime
1 0 1/1/2005
2 0 1/1/2006
3 0 1/1/2007
1 1 1/1/2005
2 1 1/1/2006

must select
obj# owner# ctime
3 0 1/1/2007
2 1 1/1/2006

i found next query to workaround

select obj#, owner#, ctime
from sys.obj$ o1
where (obj#, owner#) in (
select obj#, owner#
from (
select obj#, owner#
from sys.obj$ o2
order by o2.ctime
) x
where ROWNUM = 1
and x.owner# = o1.owner#
)

but it's very slow or this select also work but too complex

select obj#, owner#, ctime from sys.obj$ o1
where obj# = (
select obj# from (
select obj#, owner#
from sys.obj$
order by ctime) o2
where o1.owner# = o2.owner#
and rownum = 1
)

[Scorby]

The text you gave at the start strongly implies that the original problem was a missing bracket. On the query format, why not just use:

select obj#, owner#
from sys.obj$ o1
where ctime =
(select max(o2.ctime)
from sys.obj$ o2
where o2.obj# = o1.obj#
and    o2.owner# = o1.owner#)