You cannot use ORDER BY in a view.
smalus_at_my-dejanews.com wrote in message <7cjph6$e6v$1_at_nnrp1.dejanews.com>...
>In article <36E93C74.F4279FFB_at_f-m-h.com>,
> Phil Dodderidge <pdodde_at_f-m-h.com> wrote:
>> I am converting a Foxpro report to Oracle and I need to
>> figure out how to do the following:
>>
>> In Foxpro:
>>
>> Select Top 20 membno, sum(paid_amt) as paidamt
>> group by membno
>> order by paidamt desc
>>
>> Returns an ordered list of the 20 members who had the
>> largest paid claims amount.
>>
>> How do I do this in Oracle?
>>
>> I have tried using ROWNUM but this doesn't work and I can't
>> use ORDER BY in subqueries.
>>
>> I have 20-30K members and can't return them all.
>>
>> Thanks for any help,
>>
>> Phil
>>
>
>The reason ROWNUM is failing is beacuse the rownum is assigned to the row
>before the ORDER BY happens.
>
>The solution is to create a view with the ORDER BY, and select from that
view
>using the ROWNUM.
>
>Cheers,
>Shem
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Received on Mon Mar 15 1999 - 14:51:48 CST
