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

In Sybase (and Sybase alone), you can "set rowcount 3" at the beginning of your transaction, which will restrict the server to returning only 3 rows. There is nothing analogous to this in Oracle.

Brenda Muller

Xiaofeng Cheng wrote:

> 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 Fri Mar 20 1998 - 00:00:00 CST