-
Error in WHERE ...= (SELECT ... ORDER ...)
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.
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
-
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 power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
-
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:
Originally Posted by dnz63
but after remove commect on ORDER BY line i have error
ORA-00907 missing right parenthesis.
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
-
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
)
Last edited by dnz63; 01-16-2006 at 11:28 AM.
-
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#)
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|