Re: ORA-00907 : missing right parenthesis

<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. Received on Tue Nov 23 2004 - 08:50:26 CST