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
Received on Tue Nov 23 2004 - 10:43:06 CST
