Printable View
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 ?
The order by must be after the closing parenthesis.
Or is this what you want:Code:select obj#, owner#, ctime
from
(select obj#, owner#, ctime,
max(ctime) over (partition by owner#) mct
from sys.obj$)
where ctime=mct
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.
No Scorby, OP said:Quote:
Originally Posted by dnz63
Must have removed the bracket as well then.
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
)
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:
Code: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#)