Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: ORA-00907 : missing right parenthesis

Re: ORA-00907 : missing right parenthesis

From: Galen Boyer <galenboyer_at_hotpop.com>
Date: 23 Nov 2004 13:10:14 -0600
Message-ID: <uk6scjsf0.fsf@standardandpoors.com>


On Tue, 23 Nov 2004, alan_at_erols.com wrote:

> 
> "Galen Boyer" <galenboyer_at_hotpop.com> wrote in message
> news:uwtwcjz55.fsf_at_standardandpoors.com...

>> On Tue, 23 Nov 2004, alan_at_erols.com wrote:
>> >
>> > <jamesmgiordano_at_yahoo.com> wrote in message
>> > news:1101219173.539967.156610_at_c13g2000cwb.googlegroups.com...
>> >> I am using TOAD with Oracle 9i. Getting a weird error that
>> >> I don't understand.
>> >>
>> >> Not sure why this error is coming up, but I have two tables
>> >> that I am trying to run SQL on.
>> >>
>> >> select t1.table1_id,(select t2.created from table2 tr where
>> >> t1.table1_id=t2.table1_id and rownum = 1
>> >> order by t2.created) as created from table1 t1
>> >>
>> >> I am basically trying to get the newest record from my
>> >> lookup table (table2) and return all rows from table1.
>> >>
>> >> If I change the word "order" to "group" it will work, but
>> >> if I use "order" it complains. Does anyone have any ideas
>> >> why? Thanks for any help you can give me, JJ
>> >>
>> >
>> > This will not answer your question, but you have a bigger
>> > issue to solve.
>> >
>> > The first thing you need to know is that there is no such
>> > thing as the "first" or "newest" record unless you have
>> > time-dependent data of some kind (E.g., a timestamp). Tables
>> > are _sets_ of data, and as such, are technically
>> > unordered. There is no guarantee that records will be
>> > returned in the same order every time ( know- it sure looks
>> > like they are- don't be fooled). Rownum = 1 will not do
>> > what you need at all. Rownum can be used to reduce the
>> > number of records returned, but not to impose or infer an
>> > order.
>>

>> Well, almost. rownum will get him what he wants, as long as
>> it operates on the ordered set.
>>

>> --
>> Galen Boyer
> 
> 
> Only if the ordered set is ordered by something that is time
> dependent, or by whatever else it is that may be what he
> considers "first" (last name, E.g.). Anyway, I was trying to
> make the "set" point, as it did not appear that he understood
> the underlying problem.

He is ordering on create date, so he at least seemed to understand that side of it.

-- 
Galen Boyer
Received on Tue Nov 23 2004 - 13:10:14 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US