Re: Tricky SQL Problem: Top "n" of queried records

From: <chynte_at_mail.northgrum.com>
Date: 1998/03/17
Message-ID: <6em5tl$ftg$1@nnrp1.dejanews.com>#1/1

In article <6elf5h$l0c$1_at_nnrp1.dejanews.com>,   rtgraf_at_sintec.de wrote:
>
> How can I restrict the result of a SQL query to, say, the top 10 regarding
> some defined order criteria, e.g.
>
> SELECT deptno, sum(salary) FROM emp
> GROUP BY deptno
> <ONLY the top 3 w/regards to sum(salary)>;
>
> I do *not* want the client application to fetch only the first three
 records,
> I want the Server to retrieve exactly these rows.
>
> I have a rather quirky solution using a multiply nested subquery (one
 nesting
> level per "n" in the topic!)
>
> The problem is in fact a general one and I seem to have read a solution
> somewhere but forgot the source ;-(
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/ Now offering spam-free web-based newsreading
>

top ten pl sql can be obtain at www.orafaq.org.faqmain.htm under faq sql

select a.ordered_column, ......
from table1 a
where 10 >(select count(1)

                 from table1 b
                 where b.ordered_column < a.ordered_column)

order by a.ordered_column

or

    variable tenth number
    declare n number;

                cursor c1 is select sal from emp order by sal desc;
    begin
                 open c1;
                 for n in 1..10 loop
                 fetch c1 into :tenth
                 end loop;
                 close c1;
                 end;
/
      select * from emp
       where sal < :tenth
       order by sal desc;

/

hopeful this would help.

Ted Chyn

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Tue Mar 17 1998 - 00:00:00 CST