Error in WHERE ...= (SELECT ... ORDER ...)
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

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

  1. #1
    Join Date
    Jan 2006
    Posts
    2

    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 ?

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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

  3. #3
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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

  4. #4
    Join Date
    Nov 2001
    Location
    UK
    Posts
    152
    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.

  5. #5
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    No Scorby, OP said:
    Quote 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

  6. #6
    Join Date
    Nov 2001
    Location
    UK
    Posts
    152
    Must have removed the bracket as well then.

  7. #7
    Join Date
    Jan 2006
    Posts
    2
    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.

  8. #8
    Join Date
    Nov 2001
    Location
    UK
    Posts
    152
    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
  •  


Click Here to Expand Forum to Full Width