Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Tricky SQL Problem: Top "n" of queried records
rtgraf_at_sintec.de wrote in message <6elf5h$l0c$1_at_nnrp1.dejanews.com>...
>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
I have a similar problem that none of the posting addresses: performance
Suppose table "A" has a billion rows
Than:
select * from A;
Will return a billion rows:
row1's data
row2's data
...
...
And the server will run a LONG TIME before it returns. To make the point, my application want to display only the first 3 rows. When user say next, my application will display the next 3 rows, and so on and so on. The user might leave this query after watching the first 9 rows.
The whole idea is: it does not make sense for the server to retrieve a
billion
rows (which takes a long time) when user might watch only the first 9 rows.
The ideal solution is that the server will stop after 3 rows, and return the
retrieved 3
rows to user. When the "next 3" request is sent to server, server continue
to fetch
the next 3 rows. And this process goes on and on until user doen't want
any more rows or until 1 billion rows are retrieved.
Can anyone shed some light on this?
Thanks,
Xiaofeng Received on Thu Mar 19 1998 - 00:00:00 CST