Re: SQL - Select top 20 records

From: Remco Blaakmeer <remco_at_rd31-144.quicknet.nl>
Date: 16 Mar 1999 19:20:52 GMT
Message-ID: <7cmauk$vcg$1@rd31-144.quicknet.nl>

In article <36EE5FE5.3E77_at_one.net>,

        Hari Vattyam <hvattyam_at_one.net> writes:

> 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,

Assuming your table is called tableA, try something like this:

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.

> 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.

Ya think so? :-)

Remco
--
rd31-144: 8:00pm up 3 days, 2:23, 5 users, load average: 1.08, 1.14, 1.16 Received on Tue Mar 16 1999 - 13:20:52 CST