Re: SQL - Select top 20 records

From: <smalus_at_my-dejanews.com>
Date: Mon, 15 Mar 1999 20:11:27 GMT
Message-ID: <7cjph6$e6v$1@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:11:27 CST