Re: SQL - Select top 20 records

Remco Blaakmeer wrote:

> select membno,sum(paid_amt) paidamt
> from tableA t1
> where 20 > ( select count(*)
> from tableA t2
> group by membno
> having sum(paid_amt) > ( select sum(paid_amt)
> from tableA t3
> where t3.membno = t1.membno
> )
> )
> group by membno
> /
>
> Note that this query might perform badly, though, due to the double
> nested correlated subqueries.
>
> Do you know the standard 'emp' table used in many Orcale courses? I
> once
> got the question 'Which people earn the top 3 salaries?'. This was the
>
> query to get the answer:
>
> select ename, sal
> from emp e1
> where 3 > ( select count(distinct sal)
> from emp e2
> where e2.sal > e1.sal
> )
> order by sal desc
> /
>
> This would give you this:
>
> ENAME SAL
> ---------- ----------
> KING 5000
> SCOTT 3000
> FORD 3000
> JONES 2975
>
> Substituting the 'count(distinct sal)' with 'count(*)' would give you
> the three people earning the highest salaries instead of the people
> earning the three highest salaries.
>

Thanks. This is what I was looking for and I wish I could do it this way. However, with 2+ million records in my claims table I am running the original query and controlling the cursor in the calling program to only fetch the first 20 rows. It is not nearly as elegant but it is fast.

Phil Received on Fri Mar 19 1999 - 09:44:27 CST