Re: SQL - Select top 20 records

From: Finn Ellebaek Nielsen <fen_at_changegroup.dk>
Date: Fri, 26 Mar 1999 23:15:42 -0000
Message-ID: <7dh0vm$417$1@news.inet.tele.dk>

Phil Dodderdige wrote in message <36F270DA.D703591C_at_f-m-h.com>...
>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
>

In 8i it should finally be possible "directly", example:

  select rownum as rank, name, region, sum_sales   from (

      select name, region, sum(sales) as sum_sales
      from sales 
      group by name, region
      order by sum(sales) desc

    )
  where rownum <= 10;

Cheers,

Finn Received on Fri Mar 26 1999 - 17:15:42 CST