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 CET