John Haskins wrote:
>
> 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
Sounds to me like the classic ranking problem which cannot be
done in SQL only. The Fox Pro query you show actually does the
manipulation (post-processing) after the result set from the group
by is available. Your only choice seems to be to artie a procedure
which will rank the records in the order you want.
hth,
Hari
Received on Tue Mar 16 1999 - 07:43:01 CST
