Re: ORA-00907 : missing right parenthesis

"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. Received on Tue Nov 23 2004 - 11:48:51 CST